"Michael T. Babcock" <[EMAIL PROTECTED]> wrote,
> But MySQL doesn't guarantee correctness in time values in the first
> place. You can still insert "2002-02-31" as a date if you like
If you store a date in the database as a unix_timestamp in an unsigned int
column, and retrieve the value using the inverse function, errors of this
kind are self-correcting:
mysql> select unix_timestamp('2002-02-31');
+------------------------------+
| unix_timestamp('2002-02-31') |
+------------------------------+
| 1015135200 |
+------------------------------+
1 row in set (0.51 sec)
mysql> select from_unixtime(1015135200);
+---------------------------+
| from_unixtime(1015135200) |
+---------------------------+
| 2002-03-03 00:00:00 |
+---------------------------+
1 row in set (0.03 sec)
Maybe you don't want this kind of thing happening silently; in such a case
you must validate the date in the API you're using before submitting the
query.
Another benefit of using unix_timestamps: daylight saving time/standard time
issues are handled, because the unix_timestamps always relate to GMT. Date
comparisons thus can take into account time-zone offset.
-- Bruce
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
