EDIT:
Turns out it is impossible to change the DB timezone on a shared server.
So i will need to use PHP to fix my issue. Any ideas how to go about it ? Should i simply subtract 2 hours to NOW() ?
The timestamp saved in my mysql DB is off by 2 hours.
Query to save event :
$sql=query("INSERT INTO events (id, sender, time) VALUES ('','$id',NOW())");
Here's the table events in my DB:
id sender time
23 4 2013-11-03 18:57:47
The issue is that when the event occured it was 4:57pm and not 6:57pm.
How can i fix that ?
A few details that may help:
date_default_timezone_get(); outputs Europe/Berlin.
I'm in California.
This bug doesn't happen in development with localhost. Only in prod.
EDIT
When i run SELECT @@global.time_zone, @@session.time_zone; , it doesn't output anything:
@@global.time_zone @@session.time_zone
SYSTEM SYSTEM
Could be the DB server is in a different time zone. AWS DB servers, for instance, all use UTC and there's no way to change it. The only way to get around this is to set the timezone manually after to instantiate your DB connection. Not sure if this is your problem, but it could be a solution.
SET time_zone = 'US/Eastern';
You can also just use the PHP date and time: $phpDateTime = date('Y-m-d H:i:s');, then use $phpDateTime wherever you would use NOW(). As long as PHP has the right timezone setting (which you can do on the fly), this will work just fine.
you can using php variable instead now() :
$date = date('Y-m-d H:i:s');
$sql=query("INSERT INTO events (id, sender, time) VALUES ('','$id','$date')");
you also can add timezone at you php before $date = date('Y-m-d H:i:s');
Date_default_timezone_set('America/Los_Angeles');