CODEWITHSUNDEEP
phpmysqldatedatetime
user1083404
user1083404

Reputation: 23

2 hour difference in mysql column in datetime format

I have a cronjob which runs every hour. This cronjob should write to database when dataset was updated. My SQL Query in php looks like this

INSERT INTO allstats (site_id, adformat_id, imps, clicks, conv, net_pub_comp, created_at, updated_at) VALUES (?,?,?,?,?,?,"'.date("Y-m-d H:i:s").'", "'.date("Y-m-d H:i:s").'")

I print out date("Y-m-d H:i:s") and i get 2013-06-28 04:05:17 that is fully right date and time. after that i make a select now() in mysql and time is also the same. timezone on server is set to Europe/Berlin. That timezone is also set in php and mysql. everywhere i get the right time.

But why on insert the datetime column have a value which is 2 hours before the real time? I hope someone have an idea. Thanks for you help.

Upvotes: 1

Views: 822

Answers (2)

peterm
peterm

Reputation: 92795

If the datetime that you're inserting is the datetime of script execution why not just use NOW() in your insert statement?

INSERT INTO allstats (site_id, adformat_id, imps, clicks, conv, net_pub_comp, created_at, updated_at) 
VALUES (?,?,?,?,?,?,NOW(),NOW())

Less parameters to bind and pass.

Upvotes: 2

Rob W
Rob W

Reputation: 9142

MySQL date/times should be stored in the UTC time zone. Your application should calculate the difference and convert that date/time into the appropriate time zone.

Upvotes: 1

Related Questions