0

I've been reading around here and Google about the way MySQL stores datetime and date, and I can't seem to get a good understanding of it, and what I need to do to accomplish my goals. I'm also not entirely sure my host is set up correctly as it doesn't seem to behave as I expect it to.

My goal is: Datetime and Date would be stored as UTC +00:00. Then, when the database is queried with PHP, the datetime and date would be converted based on the user's settings before being displayed.

Querying the server with SELECT @@global.time_zone, @@session.time_zone; returns SYSTEM for both.

Also, if I try doing a query with SET time_zone '-05:00'; it does not change the datetime that is output from the server. Changing it to any other value also does not change it.

sharf
  • 2,123
  • 4
  • 24
  • 47

1 Answers1

0

MySQL only ever interacts with the server it's running on so it will only ever give the time zone of that server. You'd have to get the time zone on the client side.

Edit:

MySQL doesn't have a time zone literal so you can use UTC_TIMESTAMP() to get the current time in UTC or you can convert the time elsewhere.

Community
  • 1
  • 1
crownjewel82
  • 437
  • 2
  • 12
  • Right, so how would I force it to save the datetime as UTC 00:00, then convert it on pull? – sharf Sep 19 '13 at 16:13
  • Convert it to UTC before storing it. If all you need is a timestamp then use [UTC_TIMESTAMP()](http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function%5Futc-timestamp). – crownjewel82 Sep 19 '13 at 16:28
  • If I understand this correctly UTC_TIMESTAP() (instead of say, NOW()) will store the current UTC 00:00 time? Then how would I manipulate it afterwards? Is there a function in MySQL/PHP that can do it for me? – sharf Sep 19 '13 at 19:16