datetime isnt working as per the documentation

Question

I am working on a simple DB management, datetime type expected to convert the time into UTC for storing & convert back to the local time of client zone while retrieving.

But it is not working like that.

My query is here:

insert into table_name (date) values (now())

my current time is:

2015-03-23 18:42:43 (local time, IST +5:30)

I was expecting 2015-03-23 13:12:43 in the database but not so, it is just storing the same not converting to UTC.

Even in retrieving it is just giving out the same value stored in DB.

Can someone give an idea how it works ? I am not sure if I'm wrong with my query or is there something confusing ?

What is your MySQL server current timezone defined as? (http://dev.mysql.com/doc/refman/5.5/en/time-zone-support.html) — Mark Baker, Mar 23 '15 at 13:26
This may help http://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp — RiggsFolly, Mar 23 '15 at 13:39

score 0 · Answer 1 · answered Mar 23 '15 at 13:26

0

IST is +5.30 hours ahead of UTC, so while storing the data IST time to UTC you may need to use convert_tz function. Or better set the mysql server to UTC timezone.

For manual conversion it works as

mysql> select  convert_tz('2015-03-23 18:42:43','+00:00','-05:30') as utc;
+---------------------+
| utc                 |
+---------------------+
| 2015-03-23 13:12:43 |
+---------------------+
1 row in set (0.00 sec)

SO the insert will be as

insert into table_name 
(date) 
values 
(convert_tz(now(),'+00:00','-05:30'))

answered Mar 23 '15 at 13:26

Abhik Chakraborty

44,654
6
52
63

great @Abhik, your solution is for IST(India), but what for other timezones? how do we make it dynamic, so the time automatically adjusts with the client time zone. – user2398514 Mar 23 '15 at 14:18
@user2398514 thats a pretty big job. You need to store each users timezone into the DB `users` table. While they login you need to get their timezone and use that in the query. You can also do using the application level in PHP by storing the timezones as `Asia/Kolkata` thats for india and using `DateTime` function you can convert back and forth. – Abhik Chakraborty Mar 23 '15 at 14:22
Im clear with this solution, but is there a way finding client timezone, instead of storing in DB, accessing the time zone from request headers and processing it further? – user2398514 Mar 23 '15 at 14:26
You need to Javascript http://stackoverflow.com/questions/1091372/getting-the-clients-timezone-in-javascript, however this needs to be triggered using ajax and store in a PHP session variable and then use it. – Abhik Chakraborty Mar 23 '15 at 14:30

score 0 · Answer 2 · answered Mar 23 '15 at 22:24

Set your timezone correctly on both the system and MySQL and you do not need to do any conversions.

However, DATETIME is like taking a picture of a local clock, whereas TIMESTAMP is like recording the UTC of the current moment.

That is, someone in a different timezone, when reading a DATETIME will be seeing what you saw on your clock, but when reading a TIMESTAMP will see a different value.

datetime isnt working as per the documentation

2 Answers2