16

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?

Halfgaar
  • 732
  • 2
  • 7
  • 32
  • 1
    Summer time is a different time zone. E.g. CET is Central European Time, CEST is Central European Summer Time. Your OS knows when to switch. As long as you use UTC for timestamps, it is up to the presentation layer to convert that into local time. – Bart Friederichs Aug 07 '13 at 10:36

1 Answers1

9

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.

ansidev
  • 361
  • 1
  • 3
  • 17
Vatev
  • 7,493
  • 1
  • 32
  • 39
  • I know I can use timestamps, but I can't use that type. The client-side needs to be compatible with sqlite, so hence the conversion to INT. And about the invalid time; at the end of daylight saving you a certain time twice a day, which would have the same problem. – Halfgaar Aug 07 '13 at 11:25
  • There are multiple points in time with the same string representation and there are gaps too. I don't think there is anything you can do about that. Store your values as unix timestamps (INT) and convert them to local time with MySQL's functions or the time library client side. As long as you have set the timezone properly it should display correctly. – Vatev Aug 07 '13 at 11:50
  • I just tested inserting '2005-03-27 02:00:00' in a TIMESTAMP column. Retrieving it also gives you '2005-03-27 03:00:00', so I guess it's fine. – Halfgaar Aug 07 '13 at 12:55
  • One reason to want INTs https://stackoverflow.com/questions/1646171/mysql-datetime-fields-and-daylight-savings-time-how-do-i-reference-the-extra – cen Mar 31 '20 at 19:28