handling timestamps with PHP and MySQL(have I got my theory right?)

Question

Hi

I've been researching timestamps in PHP & MySQL. My undertanding is that UNIX timestamps only exist in GMT ... the whole point being that you convert it into the users timezone (GMT +/- timezone = users localtime). I'm doing this successfuly by saving the timestamp in it's "raw" form (as a 10 digit long int) and converting it into localtime in the query

$row = $result->fetchRow(DB_FETCHMODE_ASSOC); $timestamp = $row['stamp']; date_default_timezone_set('America/New_York'); echo date("d/m/y : H:i:s", $timestamp);

This appears to work fine, but I'm not sure if this is the "standard" way of doing it. Do you have any experience of this?

Answer 1

I set the timezone in php with putenv. This should set the timezone for the rest of the application if you are working based off GMT. There are probably other solutions but this is what I've found to be the easiest as I'm not always working with time and date from a database.

Answer 2

I think there is no way to specify the timezone of a datetime or timestamp field in MySQL.

So, you must store date and time in your database using one timezone and convert it to another when necessary. You can use something like Zend_Date component of Zend Framework to do the conversion.

I think there is no "standard" way to play with timezone and MySQL, you have to do your own setup.

For my clients, each one work in only one timezone so the first thing I do in my PHP script is to set the timezone with date_default_timezone_set as you said. But when doing this, you should watch for query using the function NOW() (or other date and time functions) because MySQL maybe set to another timezone than the one you used in date_default_timezone_set.

Answer 3

Your method is just fine :)

You'd probably want to set timezone in a more global scope, e.g. on whatever PHP script you are using for logins, rather than having to recall it every time you use a date function. This also makes it easier to change if, for whatever reason, you change your mind and want to standardize timezones or something along those lines.

The best way to manage Unix timestamps in MySQL is, in fact, the UNIX_TIMESTAMP() function. UNIX_TIMESTAMP() without any arguments simply returns the current Unix time so, instead of something like

$time=time();
mysql_query('UPDATE table SET time='.$time.' WHERE what="ever"');

you can just cut the extra work altogether by sticking UNIX_TIMESTAMP directly in the query:

mysql_query('UPDATE table SET time=UNIX_TIMESTAMP() WHERE what="ever"');

Much cleaner!

Answer 4

The mysql-server stores dates in a timezone independent format (UTC).
But before it stores the date it will be converted using its timezone.

U can change the mysql timezone per connection *1:

mysql_query('SET time_zone = "'.$timezone.'"');

You can also change the timezone per script.

date_default_timezone_set($timezone);

If you set them to the same timezone "2009-01-10 13:30:00" will mean the same thing to both mysql and php.

But keep in mind that the 2 servers have different internal clock values, so if you want to generate timestamps based on current time. Do that in mysql OR php.

*1) MySQL timezone support may require additional configuration. check the manual