DATETIME - three hours behind timezone

Question

Hey,

I am using DATETIME as a column type and using NOW() to insert. When it prints out, it is three hours behind. What can I do so it works three hours ahead to EST time?

I am using php date to format the DATETIME on my page.

Thanks,

Ryan

Answer 1

If the date stored in your database by using NOW() is incorrect, then you need to change your MySQL server settings to the correct timezone. If it's only incorrect once you print it, you need to modify your php script to use the correct timezone.

Edit:

Refer to W3schools' convenient php date overview for information on how to format the date using date().

Edit 2:

Either you get GoDaddy to change the setting (doubtful), or you add 3 hours when you insert into the table. Refer to the MySQL date add function to modify your date when you set it in the table. Something like date_add(now(), interval 3 hour) should work.

Your exact problem is described here.

Answer 2

Give gmdate() and gmmktime() a look. I find timestamp arithmetic much easier if you use GMT, especially if your code runs on multiple machines, or modifying MySQL server settings isn't an option, or you end up dealing with different timezones, day light savings, etc.

Answer 3

I would suggest inserting the date in UTC time zone. This will save you a lot of headache in the future (Daylight saving problems etc...)

"INSERT INTO abc_table (registrationtime) VALUES (UTC_TIMESTAMP())"

When I query my data I use the following PHP script

<?  while($row = mysql_fetch_array($registration)){ 

  $dt_obj = new DateTime($row['message_sent_timestamp']." UTC");
  $dt_obj->setTimezone(new DateTimeZone('Europe/Istanbul'));
  echo $formatted_date_long=date_format($dt_obj, 'Y-m-d H:i:s'); } ?>

You can replace the datetimezone value with one of the available php timezones here: