UNIX_TIMESTAMP no working

Question

In my database, I have the record: '2013-01-24 16:40:08'

Using the function strtotime on PHP, I get the timestamp 1359045608.

Executing the query:

SELECT UNIX_TIMESTAMP(my_timestamp_column) FROM my_table WHERE my_id_column = my_id

I get the timestamp 1359056408 and not the timestamp 1359045608.

Converting 1359056408, I get '2013-01-24 19:40:08', why!?

In my database, I use GMT - 3, but, using the function UNIX_TIMESTAMP, the database get the value in GMT. — FabianoLothor, Jan 24 '13 at 20:16

score 1 · Answer 1 · answered Jan 24 '13 at 21:03

1

I solved my problem:

SELECT UNIX_TIMESTAMP(my_timestamp_column) + TIMESTAMPDIFF(second, UTC_TIMESTAMP(), NOW())
FROM my_table
WHERE my_id_column = my_id

answered Jan 24 '13 at 21:03

FabianoLothor

2,752
4
25
39

score 0 · Answer 2 · answered Jan 24 '13 at 20:13

0

I suspect they're set to different timezones.

Compare your MySQL time with your PHP system time:

  SELECT NOW();

vs.

  <?php
      $date = date('Y-m-d H:i:s');
       echo $date;

they'll probably be 2 hours off.

answered Jan 24 '13 at 20:13

Ray

40,256
21
101
138

SELECT NOW(); = 2013-01-24 17:21:12, but... SELECT UNIX_TIMESTAMP('2013-01-24 17:21:12') = 1359058872, Converting 1359058872 = 2013-01-24 20:21:12 – FabianoLothor Jan 24 '13 at 20:22

score 0 · Answer 3 · answered Jan 24 '13 at 20:15

0

I bet you're converting the timestamp back to the date in PHP.

Look at the difference in the timestamps: 1359056408 - 1359045608 = 10800.

Now divide that by 60 seconds/minute and 60 minutes/hour:

10800 / 60 = 180
180 / 60 = 3

That means you are in a time zone that is off by 3 hours from the UTC time. You should use a function(s) that can generate timestamp in the UTC or (if that is the case) you should use a date-time storage format that is "aware" or immune to the time locality issues.

answered Jan 24 '13 at 20:15

andr

15,970
10
45
59

What exactly do you want to do with this timestamp? Do you want to display it? Be aware that your PHP and MySQL servers *both* have to be correctly set up to the same time zone. Otherwise you'll always get some discrepancies between both PHP and DB timestamps. – andr Jan 24 '13 at 20:30
I need that the timestamp generated by the PHP is equal to timestamp generated by the unix_timestamp function on MySQL. Where i can configure the timezones? – FabianoLothor Jan 24 '13 at 20:36
That depends on what server have you access to. In case it's PHP - see the answer by PoX. In case it's DB - see this answer: http://stackoverflow.com/a/10251943/1820695 – andr Jan 24 '13 at 20:38

score 0 · Answer 4 · answered Jan 24 '13 at 20:33

0

If you are using GMT on your database you can set php to use same timezone by setting date.timezone in php see date.timezone This way strtotime should use specified time zone.

answered Jan 24 '13 at 20:33

PoX

1,229
19
32

UNIX_TIMESTAMP no working

4 Answers4