1

I am trying to internationalize a database, but there are places where the timestamp that gets set is using NOW() instead of UTC_TIMESTAMP(), and it's using Amazon's RDS with multiple MySQL servers. The problem is that each is on their own timezone and this creates a mess of disentangling the various timezones, translating them to UTC, and then re-translating them back to the users timezone.

This database has some 140+ tables and I'd like something more efficient than searching manually by using SHOW CREATE on each table, plus I'd like to be able to check when I'm done that everything was converted correctly .

Any ideas on a query that would do this?

To be clear, I'm looking for a query to SELECT the rows that I need, I can figure out a INSERT... SELECT statement from that.

2

2 Answers 2

3

Using @a1ex07 answer, I used this query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_DEFAULT
  FROM INFORMATION_SCHEMA.COLUMNS 
  WHERE (table_schema, DATA_TYPE) = ('my_schema', 'timestamp') AND COLUMN_DEFAULT IS NOT NULL;

That showed that many were using CURRENT_TIMESTAMP instead of UTC_TIMESTAMP.

1

I think NOW() is what should be stored into a TIMESTAMP. As it is stored, the time is modified according to the timezone, and UTC is stored in the table. Upon retrieving, the reverse modification is performed. (This, of course, assumes the system has the 'correct' timezone established. See SHOW VARIABLES LIKE '%zone%';.)

That way, if you have users scattered around the globe, the column contains an instant in time, but each user sees it according to his own clock. That is, if I store NOW() in a TIMESTAMP, and you (wherever you are) fetch that field, it will be "now" and look like your clock.

If I store NOW() in a DATETIME, you will see what my clock said. It's like storing into a VARCHAR.

If I store UTC_TIMESTAMP() into a field of either datatype, yuck !

But, you have a messier situation, thanks to the Cloud. Perhaps setting time_zone for the session in the client would solve the problem.

Or maybe UTC_TIMESTAMP() with DATETIME is the answer. I think that would avoid Cloud issues, but all times would be UTC.

(There are about 5 models for time, not just the 2 provided by MySQL -- conf call time; sports event in another timezone, calendar implementation, etc.)

Edit

Per-connection time zones. Each client that connects has its own time zone setting, given by the session time_zone variable. Initially, the session variable takes its value from the global time_zone variable, but the client can change its own time zone with this statement:

mysql> SET time_zone = timezone;

-- http://dev.mysql.com/doc/refman/5.6/en/time-zone-support.html

2
  • "That way, if you have users scattered around the globe, the column contains an instant in time, but each user sees it according to his own clock." That would be awesome if it worked, but how does the MySQL server know what the end users timezone is? Even sending that information over, I don't see how MySQL would do an automatic conversion, it seems like a two-step dance. 1.) Figure out the difference between Server timezone and user timezone. 2.) Convert Server Timezone to User Timezone. If it's stored as UTC. 1.) Convert UTC to UserTimezone. The first step is already done. Aug 3, 2015 at 15:11
  • 1
    Added a partial answer.
    – Rick James
    Aug 3, 2015 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.