Menu
Either I'm being stupid or something's wrong here.
I have two SQL Servers, the one is on my local machine (local time +2 GMT) and the other is somewhere else (NOW() seems to return +8 GMT)and I access it through phpMyAdmin. I have a table that has a DATETIME column. I'm trying
to store the current GMT/UTC time and then display it again, still as GMT/UTC time.
Originally I stored DATE_SUB(NOW(), INTERVAL 8 HOUR) which worked just fine. However, then I read about UTC_TIMESTAMP() and liked it more, as it was shorter and the MySQL manual even said :
"The current time zone setting does not affect values displayed by functions such as UTC_TIMESTAMP() or values in DATE, TIME, or DATETIME columns."
So perfect right? Except no.
Let's assume Current GMT is 2010-02-18 17:18:17 (I even double checked it with someone in Britain).
On my local (+2) server, I get the following results for the following queries:
SELECT NOW(); 2010-02-18 19:18:17
SELECT UTC_TIMESTAMP(); 2010-02-18 17:18:17
On my online server I get:
SELECT NOW(); 2010-02-19 01:18:17
SELECT UTC_TIMESTAMP(); 2010-02-19 07:18:17 (WHY?!)
Am I missing something?!
994
Internally a MySQL timestamp column is stored as UTC but when selecting a date MySQL will automatically convert it to the current session timezone. When storing a date in a timestamp, MySQL will assume that the date is in the current session timezone and convert it to UTC for storage.
Use the getTime() method to get a UTC timestamp, e.g. new Date(). getTime() . The method returns the number of milliseconds since the Unix Epoch and always uses UTC for time representation. Calling the method from any time zone returns the same UTC timestamp.
MySQL converts TIMESTAMP values from the current time zone to UTC for storage, and back from UTC to the current time zone for retrieval. (This does not occur for other types such as DATETIME .) By default, the current time zone for each connection is the server's time.
Times are expressed in UTC (Coordinated Universal Time), with a special UTC designator ("Z"). Times are expressed in local time, together with a time zone offset in hours and minutes. A time zone offset of "+hh:mm" indicates that the date/time uses a local time zone which is "hh" hours and "mm" minutes ahead of UTC.
Probably because the clock are wrong on the online server?
Try running this:
SELECT @@system_time_zone, NOW(), UTC_TIMESTAMP()
and see which zone does it return and does it match the difference.
174
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
