WarehousePG

Appearance

timestamp9 ​

The timestamp9 module provides an efficient, nanosecond-precision timestamp data type and related functions and operators.

The WarehousePG timestamp9 module is based on version 1.2.0 of the timestamp9 module used with PostgreSQL.

Installing and Registering the Module ​

The timestamp9 module is installed when you install WarehousePG. Before you can use the data type defined in the module, you must register the timestamp9 extension in each database in which you want to use the type:

CREATE EXTENSION timestamp9;

Refer to Installing Extensions for more information.

Supported Data Types ​

The WarehousePG timestamp9 extension supports three kinds of datatatypes: TIMESTAMP9, TIMESTAMP9_LTZ and TIMESTAMP9_NTZ. (The TIMESTAMP9_LTZ data type is an alias for TIMESTAMP9 data type.)

The following table summarizes key information about the timestamp9 data types:

|Data Type|Storage Size|Description|Max Value|Min Value|Resolution| |--------|-------|----|-----------|---------|---------|----------| |TIMESTAMP9|8 bytes|Like TIMESTAMP9_LTZ. Timestamp with local time zone. |2261-12-31 23:59:59.999999999 +0000 |1700-01-01 00:00:00.000000000 +0000 | 1 nanosecond | |TIMESTAMP9_LTZ|8 bytes|Timestamp with local time zone. |2261-12-31 23:59:59.999999999 +0000 |1700-01-01 00:00:00.000000000 +0000 | 1 nanosecond | |TIMESTAMP9_NTZ|8 bytes|Timestamp without time zone. |2261-12-31 23:59:59.999999999 +0000 |1700-01-01 00:00:00.000000000 +0000 | 1 nanosecond|

More about TIMESTAMP9 ​

The TIMESTAMP9 data type is identical to the TIMESTAMP9_LTZ data type. Please see the next section for details.

More about TIMESTAMP9_LTZ ​

LTZ is an abbreviation for "Local Time Zone." WarehousePG stores TIMESTAMP9_LTZ internally in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time or GMT) time. An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone.

If no time zone is specified in the input string, then it is presumed to be in the time zone indicated by the system's TIMEZONE server configuration parameter and is converted to UTC using the offset for the time zone.

See TIMESTAMP9_LTZ Examples for examples using this data type.

More about TIIMESTAMP9_NTZ ​

NTZ is an abbreviation of ‘No Time Zone.’ WarehousePG stores UTC time internally without considering any time zone information. If a time zone is embedded in the timestamp string, WarehousePG will simply ignore it.

See TIMESTAMP9_NTZ Examples for examples using this data type.

Supported Type Conversions ​

The following table summarizes the timestamp9 module's supported type conversions.

FromToDescription
BIGINTTIMESTAMP9_LTZWarehousePG treats the BIGINT value as the number of nanoseconds started from ‘1970-01-01 00:00:00 +0000’.
DATETIMESTAMP9_LTZWarehousePG treats the DATE value as in the current session time zone. This behavior is identical to converting from from DATE to TIMESTAMPTZ.
TIMESTAMP WITHOUT TIME ZONE/TIMESTAMPTIMESTAMP9_LTZWarehousePG treats the TIMESTAMP value as in the current session time zone. This behavior is identical to converting from TIMESTAMP to TIMESTAMPTZ.
TIMESTAMP WITH TIME ZONE/TIMESTAMPTZTIMESTAMP9_LTZFor this conversion, WarehousePG only extends the fractional part to nanosecond precision.
TIMESTAMP9_LTZBIGINTThe result of this conversion is the nanoseconds since ‘1970-01-01 00:00:00.000000000 +0000’ to the given TIMESTAMP9_LTZ value. If the given TIMESTAMP9_LTZ value is before ‘1970-01-01 00:00:00.000000000 +0000’, the result is negative.
TIMESTAMP9_LTZDATEThe result of this conversion depends on the date of the given TIMESTAMP9_LTZ value in the time zone of the current session. The behavior is like doing conversion from TIMESTAMPTZ to DATE.
TIMESTAMP9_LTZTIMESTAMP WITHOUT TIME ZONE/TIMESTAMPThe result of this conversion is a timestamp without time zone. The resulting timestamp’s value is determined by the value of TIMESTAMP9_LTZ in the current session time zone. Note that the fractional part of TIMESTAMP type has 6 digits, while TIMESTAMP9_LTZ has 9 digits in its fractional part. When converting TIMESTAMP9_LTZ to TIMESTAMP, the fractional part is truncated instead of being rounded off.
TIMESTAMP9_LTZTIMESTAMP WITH TIME ZONE/TIMESTAMPWhen performing this conversion, WarehousePG truncates the fractional part to only 6 digits.
BIGINTTIMESTAMP9_NTZWhen performing this conversion, WarehousePG treats the BIGINT value as the number of nanoseconds started from ‘1970-01-01 00:00:00’.
DATETIMESTAMP9_NTZWhen performing this conversion, the resulting timestamp is ‘00:00:00.000000000’ on the given date.
TIMESTAMP WITHOUT TIME ZONE/TIMESTAMPTIMESTAMP9_NTZWhen peforming this conversion, WarehousePG only extends the fractional part to nanosecond precision.
TIMESTAMP WITH TIME ZONE/TIMESTAMPTIMESTAMP9_NTZThe resulting timestamp’s value is determined by the value of TIMESTAMPTZ in the current session time zone.
TIMESTAMP9_NTZBIGINTThe result of this conversion is the nanoseconds since ‘1970-01-01 00:00:00.000000000’ to the given TIMESTAMP9_NTZ value. If the given TIMESTAMP9_NTZ value is before ‘1970-01-01 00:00:00.000000000’, the result is negative.
TIMESTAMP9_NTZDATEWhen performing this conversion, WarehousePG truncatest the time portion and preserves the date portion.
TIMESTAMP9_NTZTIMESTAMP WITHOUT TIME ZONE/TIMESTAMPWhen performing this conversion, WarehousePG truncates only the fractional part to 6 digits.
TIMESTAMP9_NTZTIMESTAMP WITH TIME ZONE/TIMESTAMPWhen performing this conversion, WarehousePG only truncates the fractional part to 6 digits and add the time zone of the current session.
TIMESTAMP9_LTZTIMESTAMP9_NTZThe resulting TIMESTAMP9_NTZ value is determined by the value of TIMESTAMP9_LTZ in the current session's time zone.
TIMESTAMP9_NTZTIMESTAMP9_LTZWhen performing this conversion, WarehousePG adds the timezone of the current sesion.

Type Conversion Examples ​

Convert BIGINT to TIMESTAMP9_LTZ ​

=# SHOW TIMEZONE; 

   TimeZone 
-------------- 
Asia/Shanghai 
(1 row) 
	
=# SELECT 0::BIGINT::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------ 
1970-01-01 08:00:00.000000000 +0800 
(1 row)

Convert DATE to TIMESTAMP9_LTZ ​

=# SHOW TIMEZONE; 

   TimeZone 
-------------- 
Asia/Shanghai 
(1 row) 

 =# SELECT '2023-01-01'::DATE::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------- 
2023-01-01 00:00:00.000000000 +0800 
(1 row) 

=# SELECT '2023-01-01'::DATE::TIMESTAMPTZ; 

      timestamptz 
------------------------ 
2023-01-01 00:00:00+08 
(1 row)

Convert TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP to TIMESTAMP9_LTZ ​

=# SHOW TIMEZONE; 

   TimeZone 
--------------- 
Asia/Shanghai 
(1 row) 

Time: 0.411 ms 
=# SELECT '2023-01-01 00:00:00'::TIMESTAMP::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------- 
2023-01-01 00:00:00.000000000 +0800 
(1 row) 

Time: 0.691 ms 
=# SELECT '2023-01-01 00:00:00'::TIMESTAMP::TIMESTAMPTZ; 

      timestamptz 
------------------------ 
2023-01-01 00:00:00+08 
(1 row)

Convert TIMESTAMP WITH TIME ZONE/TIMESTAMP to TIMESTAMP9_LTZ ​

=# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMPTZ::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------- 
2023-01-01 00:00:00.123456000 +0800 
(1 row)

Convert TIMESTAMP9_LTZ to BIGINT ​

=# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMP9_LTZ::BIGINT; 

        int8 
--------------------- 
1672502400123456000 
(1 row) 

=# SELECT '1969-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMP9_LTZ::BIGINT; 

        int8 
-------------------- 
-31564799876544000 
(1 row)

Convert TIMESTAMP9_LTZ to DATE ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMPTZ::DATE; 
    date 
------------ 
2023-01-02 
(1 row) 

=# SET TIMEZONE TO 'UTC+0'; 
SET 
=# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMPTZ::DATE; 
    date 
------------ 
2023-01-01 
(1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP ​

Example 1

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 

      timestamp 
--------------------- 
2023-01-02 02:59:59 
(1 row) 

=# SET TIMEZONE TO 'UTC+0'; 

SET 
=# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 

      timestamp 
--------------------- 
2023-01-01 18:59:59 
(1 row)

Example 2 -- Truncation of the fractional part

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-02 02:59:59.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 

         timestamp 
---------------------------- 
2023-01-02 02:59:59.123456 
(1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP WITH TIME ZONE/TIMESTAMP ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
=# SELECT '2023-01-02 02:59:59.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMPTZ; 
          timestamptz 
------------------------------- 
2023-01-02 02:59:59.123456+08 
(1 row)

Convert BIGINT to TIMESTAMP9_NTZ ​

=# SELECT 0::BIGINT::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 

1970-01-01 00:00:00.000000000 
(1 row)

Convert DATE to TIMESTAMP9_NTZ ​

=# SELECT '2023-01-01'::DATE::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 
2023-01-01 00:00:00.000000000 
(1 row)

Convert TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP to TIMESTAMP9_NTZ ​

=# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP::TIMESTAMP9_NTZ; 
        timestamp9_ntz 

------------------------------ 
2023-01-01 00:00:00.123456000 
(1 row)

Convert TIMESTAMP WITH TIME ZONE/TIMESTAMP to TIMESTAMP9_NTZ ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMPTZ::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 
2023-01-01 00:00:00.123456000 
(1 row) 

=# SET TIMEZONE TO 'UTC+0'; 
SET 
=# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMPTZ::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 
2022-12-31 16:00:00.123456000 
(1 row)

Convert TIMESTAMP9_NTZ to BIGINT ​

=# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::BIGINT; 
        int8 
--------------------- 
1672531200123456000 
(1 row) 

=# SELECT '1969-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::BIGINT; 
        int8 
-------------------- 
-31535999876544000 
(1 row)

Convert TIMESTAMP9_NTZ to DATE ​

=# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::DATE; 
    date 
------------ 
2023-01-01 
(1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP ​

=# SELECT '2023-01-01 00:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP; 
         timestamp 
---------------------------- 
2023-01-01 00:00:00.123456 
(1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP WITH TIME ZONE/TIMESTAMP ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMPTZ; 
          timestamptz 
------------------------------- 
2023-01-01 23:00:00.123456+08 
(1 row) 

Time: 0.793 ms 
=# SET TIMEZONE TO 'UTC+0'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMPTZ; 
          timestamptz 
------------------------------- 
2023-01-01 23:00:00.123456+00 
(1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP9_NTZ ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 
2023-01-01 23:00:00.123456789 
(1 row) 

=# SET TIMEZONE TO 'UTC+0'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP9_NTZ; 
        timestamp9_ntz 
------------------------------- 
2023-01-01 15:00:00.123456789 
(1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP9_LTZ ​

=# SET TIMEZONE TO 'Asia/Shanghai'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP9_LTZ; 
           timestamp9_ltz 
------------------------------------- 
2023-01-01 23:00:00.123456789 +0800 
(1 row) 

=# SET TIMEZONE TO 'UTC+0'; 
SET 
=# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP9_LTZ; 
           timestamp9_ltz 
------------------------------------- 
2023-01-01 23:00:00.123456789 +0000 
(1 row)

The TimeZone Configuration Parameter and timestamp9 ​

You can set the TimeZone server configuration parameter to specify the time zone that WarehousePG uses when it prints a timestamp9 timestamp. When you set this parameter, WarehousePG displays the timestamp value in that time zone. For example:

sql
testdb=# SELECT now()::timestamp9;
                 now
-------------------------------------
 2022-08-24 18:08:01.729360000 +0800
(1 row)

testdb=# SET timezone TO 'UTC+2';
SET
testdb=# SELECT now()::timestamp9;
                 now
-------------------------------------
 2022-08-24 08:08:12.995542000 -0200
(1 row)

Support For Date/Time Functions ​

The timestamp9 module defines two server configuration parameters that you set to enable date/time functions defined in the pg_catalog schema on timestamp types. Visit the PostgreSQL Documentation for a list of the supported date/time functions. The parameters are:

  • timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz: when enabled, casting a timestamp9_ltz value to timestamp with time zone becomes implicit.
  • timestamp9.enable_implicit_cast_timestamp9_ntz_to_timestamp: when enabled, casting a timestamp9_ntz value to timestamp without time zone becomes implicit.

The default value for both configuration parameters is off. For example, if you try use the date function with timestamp9 and timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz is set to off:

postgres=# SELECT date('2022-01-01'::timestamp9_ltz); 
ERROR:  implicitly cast timestamp9_ltz to timestamptz is not allowed 
HINT:  either set 'timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz' to 'on' or do it explicitly

Enable the configuration parameter in order to use the date function:

postgres=# SET timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz TO 'ON'; 
SET 
postgres=# SELECT date('2022-01-01'::timestamp9_ltz); 
    date 
------------ 
 01-01-2022 
(1 row)

Note that enabling these configuration parameters will also result in multiple casting paths from timestamp9 types and built-in timestamp types. You may encounter error messages such as:

postgres=# select '2019-09-19'::timestamp9_ltz <= '2019-09-20'::timestamptz; 
ERROR:  operator is not unique: timestamp9_ltz <= timestamp with time zone 
LINE 1: select '2019-09-19'::timestamp9_ltz <= '2019-09-20'::timesta... 
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

In this situation, cast the type explicitly:

postgres=# select '2019-09-19'::timestamp9_ntz <= '2019-09-20'::timestamptz::timestamp9_ntz; 
?column? 
---------- 
 t 
(1 row)

Alternatively, cast the timestamp9_ntz value to the timestamptz value:

postgres=# select '2019-09-19'::timestamp9_ntz::timestamptz <= '2019-09-20'::timestamptz; 
?column? 
---------- 
 t 
(1 row)

Support For Date/Time Functions ​

The timestamp9 module defines two server configuration parameters that you set to enable date/time functions defined in the pg_catalog schema on timestamp types. Visit the PostgreSQL Documentation for a list of the supported date/time functions. The parameters are:

  • timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz: when enabled, casting a timestamp9_ltz value to timestamp with time zone becomes implicit.
  • timestamp9.enable_implicit_cast_timestamp9_ntz_to_timestamp: when enabled, casting a timestamp9_ntz value to timestamp without time zone becomes implicit.

The default value for both configuration parameters is off. For example, if you try use the date function with timestamp9 and timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz is set to off:

postgres=# SELECT date('2022-01-01'::timestamp9_ltz); 
ERROR:  implicitly cast timestamp9_ltz to timestamptz is not allowed 
HINT:  either set 'timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz' to 'on' or do it explicitly

Enable the configuration parameter in order to use the date function:

postgres=# SET timestamp9.enable_implicit_cast_timestamp9_ltz_to_timestamptz TO 'ON'; 
SET 
postgres=# SELECT date('2022-01-01'::timestamp9_ltz); 
    date 
------------ 
 01-01-2022 
(1 row)

Note that enabling these configuration parameters will also result in multiple casting paths from timestamp9 types and built-in timestamp types. You may encounter error messages such as:

postgres=# select '2019-09-19'::timestamp9_ltz <= '2019-09-20'::timestamptz; 
ERROR:  operator is not unique: timestamp9_ltz <= timestamp with time zone 
LINE 1: select '2019-09-19'::timestamp9_ltz <= '2019-09-20'::timesta... 
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.

In this situation, cast the type explicitly:

postgres=# select '2019-09-19'::timestamp9_ntz <= '2019-09-20'::timestamptz::timestamp9_ntz; 
?column? 
---------- 
 t 
(1 row)

Alternatively, cast the timestamp9_ntz value to the timestamptz value:

postgres=# select '2019-09-19'::timestamp9_ntz::timestamptz <= '2019-09-20'::timestamptz; 
?column? 
---------- 
 t 
(1 row)

Examples ​

TIMESTAMP9_LTZ Examples ​

Valid input for TIMESTAMP9_LTZ ​

Valid input for the TIMESTAMP9_LTZ consists of the concatenation of a date and a time, followed by an optional time zone. Users can specify the fractional part of second up to 9 digits (in nanosecond precision).

The current system’s TIMEZONE parameter is ‘Asia/Shanghai’ 

SELECT '2023-02-20 00:00:00.123456789 +0200'::TIMESTAMP9_LTZ; 

           timestamp9_ltz 

------------------------------------- 
2023-02-20 06:00:00.123456789 +0800 
(1 row) 

 If the input string doesn’t have explicit time zone information, the timestamp is presumed to be in the time zone indicated by the system’s TIMEZONE parameter. 

SELECT '2023-02-20 00:00:00.123456789'::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------- 
2023-02-20 00:00:00.123456789 +0800 
(1 row)

TIMESTAMP9_LTZ also accepts numbers as valid input. It’s interpreted as the number of nanoseconds since the UTC time ‘1970-01-01 00:00:00.000000000’.

SELECT '123456789'::TIMESTAMP9_LTZ; 

           timestamp9_ltz 
------------------------------------- 
1970-01-01 08:00:00.123456789 +0800 
(1 row)

TIMESTAMP9_NTZ Examples ​

Valid input for TIMESTAMP9_NTZ ​

As with TIMESTAMP9_LTZ, valid input for the TIMESTAMP9_NTZ data type consists of the concatenation of a date and a time, followed by an optional time zone. Users can specify the fractional part of second up to 9 digits (in nanosecond precision). The difference is that, if the user specifies time zone in the input string, TIMESTAMP9_NTZ will ignore it and store the remaining timestamp as UTC time without applying any time zone offset.

The current system’s TIMEZONE parameter is ‘Asia/Shanghai’

=# SELECT '2023-02-20 00:00:00.123456789 +0200'::TIMESTAMP9_NTZ;
        timestamp9_ntz
-------------------------------
2023-02-20 00:00:00.123456789
(1 row)
=# SELECT '2023-02-20 00:00:00.123456789'::TIMESTAMP9_NTZ;
        timestamp9_ntz
-------------------------------
2023-02-20 00:00:00.123456789
(1 row)

Limitations ​

The timestamp9 data type does not support arithmetic calculations with nanoseconds.