MYSQL find if a default value has been set for a table (DateTime Specifically)

Question

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.

Answer 1

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.

Answer 2

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