How to make UNIX_TIMESTAMP not shift the datetime field in different time zones?

Question

I created a small forum where users can post. My server is in the United States, but the forum base is in Taiwan (+ 15 hours).

When someone submits to the form, I save the time in my mySQL database in the format YYYY-MM-DD HH: MM: SS. When I look at the database, the time displays the correct time (the time that a person in Taiwan sent).

However, when I use UNIX_TIMESTAMP to get the date from the database, the time changes.

Example:

• I am posting something on the forum. The date on my watch is 2009-10-2 11:24 (Taiwan time).
• I look in the database, and he says that the date-time is 2009-10-2 11:24 (at the same time as my watch!)!
• Then, when I use UNIX_TIMESTAMP to display the date on my website, it displays as 2009-10-03 4:22 pm (poor use of bias).

Is there a way to get UNIX_TIMESTAMP to stop converting time (applying an offset) when I request a date from the database?

Additional Information:
I am using php
I set the time zone in my PHP in Taiwan (date.timezone = Asia / Taipei)
If the user is in a different time zone than Taiwan, I want him to convert the time to Taipei. The site is almost 100% used by Taiwan, so I just want Taiwan time to show all the time, even if they are in a different time zone.
I display the date in many areas around the site in different date formats ().
Basically, everything works fine, except that when I use UNIX_TIMESTAMP to query data, it applies an offset to time.

Thanks!

Answer 1

I found a possible solution, which is to simply get the date from the database without converting it to Unix time, and then just using strtotime (); to convert it to Unix time. Basically, instead of converting using sql, I convert using php. The only thing I don't like about this is: strtotime () <I'm not sure how reliable this function is, and I need to go and change about 100 places where I use UNIX_TIMESTAMP (doh!)

Are there any other ways?

Answer 2

MySQL writes "as-is" dates, also reads them like this, but UNIX_TIMESTAMP processes any input dates, as in your local time zone, and converts them to UTC / GMT time stamps, which means that it will apply your local time zone offset, now, timestamps returned from mysql, for example. php date () it will apply your local timezone offset again (note that there is also gmtime () which does not), which will lead to undesirable results.

But you can go through with the following trick that subtracts the session time zone before UNIX_TIMESTAMP () applies it, so you will get the exact number regardless of the server / local time zone if you want the exact same date in db as if it’s GMT.

mysql> SELECT UNIX_TIMESTAMP(CONVERT_TZ("2013-05-27","GMT",@@session.time_zone)); +--------------------------------------------------------------------+ | UNIX_TIMESTAMP(CONVERT_TZ("2013-05-27","GMT",@@session.time_zone)) | +--------------------------------------------------------------------+ | 1369612800 | +--------------------------------------------------------------------+ 1 row in set (0.00 sec) 

Another solution would be to set the servers or the time zone of the session to 0 (GMT), so there will be no real conversions.

Answer 3

MySQL accepts the system default time zone setting, unless otherwise specified, explains your problems; see a more detailed description of MySQL timezone . Based on my past experience, I came to the conclusion that UTC is the best choice for storing date and time; when displayed to the user, the user is converted to the user's time zone.

If possible, change all the date and time records in the database to UTC, set the time zone in PHP using date_default_timezone_set() and make sure that you converted it correctly when rendering the user and when storing it in the database. If storing UTC values ​​is not an option, you can simply convert them by following the time zone reference in the same way as with UTC.

What you need to do is grab the raw date and time from the database, and then use PHP DateTime to convert it. Take a look at DateTimeZone .

Answer 4

The best I have found for this problem is:

SELECT UNIX_TIMESTAMP (CONVERT_TZ (<lt; →, '+ 15:00', '+ 00:00')) + TIMESTAMPDIFF (second, utc_timestamp (), now ())

Example: I want to get the timestamp on May 31, 2012 at 23:59:59, local time. SELECT UNIX_TIMESTAMP (CONVERT_TZ ('2012-05-31 23:59:59', '+ 15:00', '+ 00:00')) + TIMESTAMPDIFF (second, utc_timestamp (), now ())

Thus, I get GMT-0 timestamp corresponding to local time.