2

I made a post a couple days ago and used the advice given from the responses. I have a large scale web application which was hosted on a server in our timezone, where I used all datetime/timestamps to the current timezone. I should have used UTC and converted it on the code level to the appropriate timezone.

Well I am doing that now but I am having issues. The server I am using now is not in my timezone and the TIMESTAMP (on insert) is set to their system (O/S) time. Not the UTC time.

Why doesn't:

SET time_zone = '+00:00';

Fix the insert TIMESTAMP value? How can I ensure the MySQL server defaults the timestamps to UTC?

SOLVED:

This is really confusing because MySQL automatically converts and displays TIMESTAMP's in the database to the current timezone, so all my TIMESTAMPS were converted and displayed to the SYSTEMS timezone. When in reality ALL TIMESTAMPS are stored as UTC. So if you go:

SET SESSION time_zone = '+00:00'; SELECT * FROM what_ever_table;

It will display the timestamp in that timezone.

arogachev
  • 33,150
  • 7
  • 114
  • 117
user1627928
  • 313
  • 2
  • 4
  • 13
  • Possible duplicate of http://stackoverflow.com/questions/4562456/mysql-setting-time-zone-in-my-cnf-options-file – Bryan Oct 25 '12 at 14:54
  • 2
    @BryanMoyles No. I am not the owner of the MySQL server so I cannot change the configuration files. – user1627928 Oct 25 '12 at 15:18

1 Answers1

-3

MySQL doesn't care about timezones when inserting/updating values. all it sees are time/date values and strings. There is NOT timezone data included with its standard date format: yyyy-mm-dd hh:mm:ss. Timezones only enter into the picture when you're retrieving the data and want to format the values, e.g. using the convert_tz() function

Marc B
  • 356,200
  • 43
  • 426
  • 500
  • 1
    I don't understand. When I insert a record: 'timestamp No CURRENT_TIMESTAMP' it uses the systems (O/S) timezone to set the datetime. Which is different than UTC. But I want it to be UTC. – user1627928 Oct 25 '12 at 14:58
  • if I insert the current date/time, `2012-10-25 09:00:00` for me, how is mysql supposed to know that this is gmt-6, and not perhaps a date from some other timezone? there is NO tz data in a standard mysql date string. it's up to you to attach tz meaning to the stored date/time values. – Marc B Oct 25 '12 at 15:01
  • 3
    "how is mysql supposed to know that this is gmt-6, and not perhaps a date from some other timezone?" because it uses the SYSTEM's timezone. What are you talking about. – user1627928 Oct 25 '12 at 15:02
  • 2
    -1 This is not true for timestamp fields. MySQL converts date+time strings (stored in timestamp fields) to UTC using its configured timezone setting (by default read from the SYSTEM). When you read & write timestamp fields MySQL converts between timezones based on this configured timezone. Assuming no MySQL convert functions are used. Note that datetime fields are not converted & are stored as given. – Precastic Jan 20 '16 at 09:20