Mysql converting TIMESTAMP to INTEGER - timezones

Question

I need to convert some TIMESTAMP fields to INT in our MySQL (InnoDB) DB. I realize that converting a TIMESTAMP to INT is unusual, but we still need to do it :)

It seems straight-forward enough to do, but there are some timezone and daylight saving errors.

I have a script that generates my SQL code per column. For example, it generates:

ALTER TABLE alarmLog ADD COLUMN started_tmp INT UNSIGNED;
UPDATE alarmLog SET started_tmp = UNIX_TIMESTAMP(started);
ALTER TABLE alarmLog DROP started;
alter TABLE alarmLog CHANGE started_tmp started INT UNSIGNED NULL DEFAULT 0;

If I compare the before and after data using select FROM_UNIXTIME(1291788036);, the result looks good.

The idea is then to change all the client-side software to convert to UTC and use that INT when storing it. When retrieving, that INT is converted to the current time zone.

But then the docs warn me about this scenario (daylight savings in CET):

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 02:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 02:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT UNIX_TIMESTAMP('2005-03-27 03:00:00');
+---------------------------------------+
| UNIX_TIMESTAMP('2005-03-27 03:00:00') |
+---------------------------------------+
|                            1111885200 |
+---------------------------------------+
1 row in set (0.00 sec)

How do API and OS's normally deal with daylight savings? I know my PC has its clock in UTC and in summer time, the OS adds two hours to it, and in winter time one. I assume it uses the UTC time to determine whether it's DST or not.

So, how do I deal with this? Is the only solution to add a field to the database to specify DST offset?

Answer 1

You don't need to store the time in INT's. MySQL's TIMESTAMP type does that anyway (it uses standard Unix timestamps to store the time) and they are always in UTC timezone.

You only need to set the session timezone and all TIMESTAMP columns will be converted from/to your zone when you update/select them.

You can set the zone at connect/initialization time once:

SET time_zone = '+10:00';

And then you can select/update the time in your zone directly

SELECT timestamp_column FROM table ...

I'm not very familiar with datetime libs but I guess they use the timezone you provided and the time in question to determine timezone and daylight savings offsets.

In the example you provided I think one of the values is actually invalid, because the clock is supposed to jump from 01:59:59 to 03:00:00 and 02:00:00 never actually happened. The UNIX_TIMESTAMP function probably returns the nearest second in that case.