0

I have a MySQL field that gives me timestamp in the format: YYYY-MM-DD HH:MM:SS. The default is CURRENT_TIMESTAMP. Now, when I try to convert it to Unix timestamp, I get some weird results.

Actually, I am using this function to calculate relative time. It outputs negative integers even though MySQL timestamp is only a couple of seconds older. I am at a loss as to what's wrong.

I also found some weird issues. For example, in MySQL,

mysql> select from_unixtime(1289206455);
+---------------------------+
| from_unixtime(1289206455) |
+---------------------------+
| 2010-11-08 14:24:15       |
+---------------------------+
1 row in set (0.03 sec)

In PHP CLI:

php -r "echo date('Y-m-d h:m:s', 1289206455);"
2010-11-08 09:11:15

The supposed output (in both cases) should be: 2010-11-08 02:54:15 source.

So there seems to be a difference in times in PHP and MySQL. How can I sync them? I am on Ubuntu 11.04 XAMPP.

Thanks

Community
  • 1
  • 1
abhisek
  • 924
  • 1
  • 13
  • 27

1 Answers1

0

You need to set the timezone.

date_default_timezone_set($zone);

http://php.net/manual/en/function.date-default-timezone-set.php

MySQL: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_time_zone (@Idg thanks)

Atticus
  • 6,585
  • 10
  • 35
  • 57
  • for mysql: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_system_time_zone – ldg Sep 20 '11 at 03:29