PHP use CURRENT_TIMESTAMP from eastern time zone

Question

I have a query that looks like:

$query = "INSERT INTO  `SmsServ`.`SmsReceived` (
    `Id` ,
    `Body` ,
    `DateReceived`
)
VALUES ( NULL , "Foo" , CURRENT_TIMESTAMP);"

The problem with that query is that I will like to have eastern time as the time stamp. I have tried calling:

date_default_timezone_set("America/New_York");

At the begining of my php script but that still gives an incorrect time stamp.

Answer 1

CURRENT_TIMESTAMP in this case is not generated by PHP, but by MySQL. Your query is asking MySQL to set the current timestamp based on MySQL's server time. As such you would need to configure MySQL to use the eastern time zone, not PHP.

One thing you might consider is to just use GMT for database timestamps and do timezone and daylight savings conversions in the application. That way you don't potentially have the issue of mixed zone timestamps in the database. Of course, if you don't think you would ever have need to use anything other than Eastern time zone in your app, then this might not be important for you.

Answer 2

Mysql is not concerned about which timezone is using ..

As per documentation you can set the timezone to be used....if you are using MySQL 4.1.3 or +

SET GLOBAL time_zone = timezone

 or

SET time_zone = timezone

NOTE : TIMEZONE settings are not populated by default. You need to populate the tables related to timezones under `mysql database . oNly after the settings you can set the timezone.

Answer 3

You did change the php time zone. So try to change the mysql time zone aswell(php and mysql time default zones differs from each other):

mysql> SET GLOBAL time_zone = 'America/New_York';

mysql> SET SESSION time_zone = 'America/New_York';