How to get UNIX_TIMESTAMP to not offset a datetime field when in different time zones?

Question

I have built a small forum where users can post messages. My server is in the United States, but the userbase for the forum is in Taiwan (+15hrs).

When someone posts to the form, I store the time in my mySQL database in the format YYYY-MM-DD HH:MM:SS. When I look in the database, the time displays the proper time (the time that the person in Taiwan posted it).

However, when I use UNIX_TIMESTAMP to get the date out of the database, the time is altered.

Example:

1. I post something to the forum. The datetime on my wrist watch is 2009-10-2 11:24am (Taiwan Time)
2. I look in the database and it says the datetime is 2009-10-2 11:24am (same time as my wrist watch. good!)
3. Then when I use UNIX_TIMESTAMP to display the date on my website, it shows as 2009-10-03 4:22 pm (bad! it applied an offset)

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

Extra Info:
I'm using PHP
I have set the timezone in my PHP to Taiwan (date.timezone = Asia/Taipei)
If a user is in another timezone than Taiwan, I want it to convert the time to Taipei time. The site is nearly 100% Taiwan used so I just want Taiwan time to show all the time even if they're in another timezone.
I display the date in lots of areas around the site in different date() formats.
Basically everything works great except that when I use UNIX_TIMESTAMP to query the data out, it applies an offset to the time.

Thanks!

Answer 1

MySQL writes dates "as-is", also reads them so, but UNIX_TIMESTAMP treats any input dates as in your local timezone and converts them to UTC/GMT timestamps meaning it will apply your local timezone offset, now if you process your timestamps returned from mysql via eg. php date() it will again apply your local timezone offset(note there is also gmtime() which does not do that), which will produce unwanted results.

But you can get by with this following trick which will subtract your session timezone before UNIX_TIMESTAMP() applies it, so you will get the exact number regardless of the server/local timezone if you want the exact same date in db as if it were a GMT time.

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 session timezone to 0(GMT), so there will be no real conversions taking place.

Answer 2

MySQL takes system's default timezone setting unless told otherwise, it explains the problems you are having; take a look at MySQL's time zone reference manual for more details. Based on my past experience I've come to a conclusion UTC is the best choice for storing date and time; when displaying it to the user, they are converted to user's timezone.

If possible, change all date and time entries in the DB to UTC, configure timezone in PHP usingdate_default_timezone_set()and make sure to convert it properly when rendering it to the user and when storing it in the database as well. If storing UTC values is not an option, you may simply convert them by following time zone reference guide the same way as with UTC.

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