How to Use the ABSTIME Data Type in nzsql

Question & Answer

Question

How do you use the ABSTIME data type in nzsql?

Answer

Although the ABSTIME data type can be difficult to use, the following procedure creates a column with a TIMESTAMP format and a converted value that is the same as the ABSTIME.

1. Find out the difference between GMT and local time by running the following query:

select CREATEDATE, CREATEDATE::timestamp from _v_user;

The output will be like this:

CREATEDATE | ?COLUMN?

---------------------+---------------------

2009-06-26 07:34:37 | 2009-06-26 11:34:37

2009-08-12 13:15:03 | 2009-08-12 17:15:03

2009-08-12 13:15:03 | 2009-08-12 17:15:03

2009-08-12 13:15:03 | 2009-08-12 17:15:03

2009-08-12 13:15:03 | 2009-08-12 17:15:03

2009-08-12 13:15:03 | 2009-08-12 17:15:03

2009-08-12 13:15:04 | 2009-08-12 17:15:04

2009-08-12 13:15:04 | 2009-08-12 17:15:04

2009-08-12 13:15:04 | 2009-08-12 17:15:04

...

You will see that the values in the two columns of your output are some number of hours apart from each other. In this case, the difference is 4 hours.

2. Now, using the above difference, convert abstime into a timestamp:

Select CREATEDATE,

CREATEDATE::timestamp – interval ‘4 hours’ as CREATEDATE_AS_TIMESTAMP

from _v_user;

Here is what you will get:

CREATEDATE | CREATEDATE_AS_TIMESTAMP

---------------------+-------------------------

2009-06-26 07:34:37 | 2009-06-26 07:34:37

2009-08-12 13:15:03 | 2009-08-12 13:15:03

2009-08-12 13:15:04 | 2009-08-12 13:15:04

This creates a new column formatted as timestamp with a value that is the same as the abstime.

3. Use that new column in your date manipulations instead of CREATEDATE:

select CREATEDATE::timestamp - interval '4 hours' from _v_user left outer join another_table on _v_user.objid=another_table.objid;

[{"Product":{"code":"SSULQD","label":"IBM PureData System"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Database","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"1.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Historical Number

NZ909025

Was this topic helpful?

Document Information

Modified date:
17 October 2019

UID

swg21578285

Tips