Posted by Michael on 10/31/07 04:28

I believe it depends on what the OP is storing. If it is a Timestamp I
do not see ANY need for dates out side 1970-2038. There is almost no
reason to waste 4 bytes. I am aware that MySQL has fantastic date
operations, etc, but the OP needs to ask himself if he needs these.

Datetime takes up twice as much memory as a timestamp.

I don't think DATETIME is 100% right for every date or time that needs
to be stored but, that is my opinion.

In the case of timestamps, email sent times, page last update times, I
think a UNIX_TIMESTAMP is a very good choice. Your probably going to
convert it anyway to do any calculations, I couldn't really see you
doing 'SELECT DATE_FORMAT(DATETIME, '%a');' just to get 'Sun'. This is
probably a bad example because if you wanted the day abbreviation you
probably would have had it in the fields list anyway.

I will agree if you need dates outside the unix epoch, OR you need to do
a lot of heavy calculations, like finding all transactions from a
particular day name, then you should use a DATETIME field.

Sorry, I do not mean to come on here and start a flame war. The OP had a
question about timestamps, and timezones I am simply trying to answer
them without getting sidetracked onto what is the best way to do X.

Michael Fesser wrote:
> .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]