This is one of the items, which we need to do in Netezza SQL from time to time, but is not exactly obvious to the average SQL user. So, having a pattern for making the conversion for an epoch field to a timestamp can be a real time saver.
Epoch Field Conversion to A Timestamp Format
to_timestamp(‘1970-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’) + (<> * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format
Epoch Timestamp Conversion Example SQL 1
select
entry_ts as Orginial_Format_Entry_TS,
to_timestamp(‘1970-01-01 00:00:00′,’YYYY-MM-DD HH24:MI:SS’) + (entry_ts * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format
from _v_system_util
limit 1;
Epoch Timestamp Conversion Example SQL 2
select
entry_ts as Orginial_Format_Entry_TS,
to_date(‘1/1/1970′,’MM/DD/YYYY’) + (entry_ts * interval ‘.000001 seconds’) as Entry_TS_Timestamp_Format
from _v_system_util
limit 1;
