Posted by Michael Fesser on 10/29/07 17:23

..oO(Michael)

>Your suggestion of using a MySQL DATETIME field because they allow dates
>from 0000-9999 is then contradicted by saying 'so you might want to go
>via Unix timestamp to get back to a local time using set_locale() and
>date().'.
>
>There is no point of storing it in a DATETIME field if you are going to
>be using a UNIX_TIMESTAMP() call anywhere.

DATE/DATETIME are the correct types for storing dates in the DB, regardless
of what you're going to do with them.

If you store dates, then it's also quite likely that there will be some
kind of date calculations or particular queries which involve these dates
in their WHERE part. Using the correct type makes it much easier and allows
to let the DB do most of the work. The receiving script then just has to
take care of the output.

>You are right to store the UNIX timestamp from TIME() in the database as
>an integer. The timestamp will always be in GMT and is easily worked
>with and converted amongst timezones.
>
>I'd suggest not to use MySQL DATETIME unless you specifically require
>dates that cannot be stored in a UNIX Timestamp.

Why not?

>Using a MySQL DATETIME field can be troublesome with timezones

As of MySQL 4.1.3 there's improved time zone support, even on a per-
connection basis if necessary.

Micha

[Back to original message]