Search code examples
mysqltimestampunix-timestamp

FROM_UNIXTIME to MySQL return wrong date

I just insert to MySQL using FROM_UNIXTIME but it gives wrong date. My query was 

INSERT INTO `phpc_occurrences`
SET `eid` = '15', 
    `time_type` = '0', 
    `start_ts` = FROM_UNIXTIME('1479189600'), 
     `end_ts` = FROM_UNIXTIME('1479232800');

1479189600 should return Tue, 15 Nov 2016 06:00:00 GMT but my local MySQL database returns Tue, 15 Nov 2016 13:00:00 GMT

My PC timezone is UTC +7 but I already change it to UTC 0 and the date from MySQL still return the wrong value.

Can anybody please explain what is happening?

Solution

  • Please be sure you update your time_zone variable to GMT. For example, my current time_zone is GMT+03, so i get:

    select FROM_UNIXTIME('1479189600');
+-----------------------------+
| FROM_UNIXTIME('1479189600') |
+-----------------------------+
| 2016-11-15 09:00:00         |
+-----------------------------+

    but changing time_zone to UTC(GMT) value gives other result:

    set time_zone='+00:00';
select FROM_UNIXTIME('1479189600');
+-----------------------------+
| FROM_UNIXTIME('1479189600') |
+-----------------------------+
| 2016-11-15 06:00:00         |
+-----------------------------+

    Maybe mysql didn't update it's inner time_zone variables with your system time zone ?

    Also you should note if your fields 'start_ts' and 'end_ts' have TIMESTAMP types. If it is so, you can no worry about timestamp when inserting into table, it matters only when you selecting some data, because your mysql client actually do convert from TIMESTAMP value (which is actually just an int32) to your current local time, so you need to hint your db-client with your desired timezone.