Netezza: convert ABSTIME into current time zone timestamp.

PROBLEM:

/* ERROR [HY000] ERROR:  Type 'abstime' not supported by IBM Netezza SQL or has invalid size */

 CREATE TABLE MY_V_TABLE AS SELECT * FROM _V_TABLE;

At Netezza, management tables and system views have ABSTIME internal datatype this is a subset of GMT timestamp.  Creating a table as CTAS with have thei datatype will result in error.

---

SOLUTION:

Converting internal ABSTIME into your local time zone TIMESTAMP solves the problem.

SELECT CREATEDATE                                              as "ORIGINAL_DATATYPE_ASTIME"

      ,CREATEDATE  + ( select TZOFFSET from _VT_PG_TIME_OFFSET)  as "LOCAL_TIMEZONE_TIMESTAMP"

      ,CREATEDATE -INTERVAL'6 HOUR'                            as "LOCAL_TIMEZONE_TIMESTAMP"

  FROM _V_TABLE;

Notice: the management view _VT_PG_TIME_OFFSET shows the local time zone of physical location of Netezza Appliance box. it might be in different timezone.

select * from _VT_PG_TIME_OFFSET;