Netezza / PureData – How to convert an epoch field to a timestamp

Netezza PureData convert an epoch field to a timestamp

How To Convert And Epoch Field To A Timestamp

This is one of the item, 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 a 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;

Related References