Netezza: 'abstime' datatype conversion

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 will result in error with ABSTIME column.

---

SOLUTION:

Alternative 1:

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

;

Work around is adding interval of 0 seconds or N-hours to match to your time zone:

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;

---

Alternative 2: Use timezone(starttime, 'UTC','EST' )  function: 

select createdate                                                      as ORIG_TIMESTAMP

    ,timezone(createdate , 'UTC','EST' )                              as DATE_ETC_V1

    ,timezone(createdate , 'UTC','America/New_York' )                 as DATE_ETC_V2

    ,date_trunc( 'day',timestamp (timezone(createdate ,'UTC','EST')))    as TRUNC_2_DATE_ETC

    ,createdate + ( select TZOFFSET from _VT_PG_TIME_OFFSET)             as DATE_ETC_VIA_TIME_OFFSET

FROM SYSTEM .._V_TABLE

;