Mysql converting TIMESTAMP to INTEGER - timezones -

i need convert timestamp fields int in our mysql (innodb) db. realize converting timestamp int unusual, still need :)

it seems straight-forward enough do, there timezone , daylight saving errors.

i have script generates sql code per column. example, 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 compare before , after data using select from_unixtime(1291788036);, result looks good.

the idea change client-side software convert utc , use int when storing it. when retrieving, int converted current time zone.

but docs warn me 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 api , os's deal daylight savings? know pc has clock in utc , in summer time, os adds 2 hours it, , in winter time one. assume uses utc time determine whether it's dst or not.

so, how deal this? solution add field database specify dst offset?

you don't need store time in int's. mysql's timestamp type anyway (it uses standard unix timestamps store time) , in utc.

you need set session timezone , timestamp columns converted from/to zone when update/select them.

you can set zone @ connect/initialization time once:

set time_zone = '+10:00'; 

and can select/update time in zone directly

select timesamp_column table ... 

i'm not familiar datetime libs guess use timezone provided , time in question determine timezone , daylight savings offsets.

in example provided think 1 of values invalid, because clock suppose jump 01:59:59 03:00:00 , 02:00:00 never happened. unix_timestamp function returns nearest second in case.