The best way to store date/time in MySQL for use with 'date' helper?

[eluser]Dan Murfitt[/eluser]
Hi

I just wondered what people are using to store times/dates in MySQL for use with the CI 'date' helper?

I'm building a site with user specific timezone support, so I need to use the gmt_to_local() function. Normally I would use 'datetime', but I'd have to convert the datetime string back to a unix timestamp every time, so maybe int(11) would be better?

Any advice on this appreciated

Many thanks
Dan

[eluser]Unknown[/eluser]
TIMESTAMP ?

[eluser]WanWizard[/eluser]
Store all date information in the database, as a timestamp, in GMT.
To deal with the conversion to and from a GMT date, it depends on your requirements.

If you don't care about correct conversions, or about dates in the past, you can use CI's simple date functions.

If you do care, and your using PHP 5.2+, you can use PHP's DateTime class to convert from local to GMT and back. Check out our date helper.

If you're using pre PHP 5.2, you're out of luck, since you would have to emulate the DateTime class. If you use our date helper, you will need our timezone helper as well. Note that it requires lots of memory.

[eluser]glopv2[/eluser]
What about the year 2038 problem? It's made me squeamish about timestamps

http://en.wikipedia.org/wiki/Year_2038_problem

[eluser]WanWizard[/eluser]
There are a few sides to this issue.

First, you have the storage issue. 2038 is an issue because a timestamp is the number of seconds since 1-1-1970, and as the timestamp is a 32-bit integer, you'll run out of seconds to store in 2038. Solution, don't store it in 32-bits. For database fields, that means UNSIGNED INT(11).

Second issue is that the functions dealing with timestamps, all use this same 32-bit integer. Therefore things like "echo strtotime("11-11-2050");" don't work. This has been fixed some time ago for 64-bit platforms, so if you're using PHP on a 64-bit OS (both Linux and Windows), it's not a problem.
If you find a function that hasn't been fixed, in PHP5, you can (again) use the DateTime() class, as it uses 64-bit integer internally.

edit:
Just tested this on my development machine (Fedora 13 x86_64, PHP 5.3.2):

Code:

echo strtotime("11-11-2050"),'<br>';
echo date("Y-m-d", 2551734000);

Results

Code:

2551734000
2050-11-11

So no issues there...

[eluser]WanWizard[/eluser]
Forgot, same if you want to go before 1-1-1970. You can use negative timestamps these days if you want to go back.
Don't forget to remove the 'unsigned' on your database field if you do.

[eluser]danmontgomery[/eluser]
You can use mysql's datetime instead of timestamp if you're unsure about the date range. Storage costs 8 bytes instead of 4, no timezone conversion happens and you lose the "on update current_timestamp" functionality. Per http://dev.mysql.com/doc/refman/5.0/en/datetime.html, the range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Other than that they are functionally the same.