Tanzu Greenplum 6

PDF
7 6 5

timestamp9

Last Updated March 11, 2025

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

The Greenplum Database 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 Greenplum Database. 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 Additional Supplied Modules for more information.

Supported Data Types

The Greenplum Database 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." Greenplum Database 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.’ Greenplum Database stores UTC time internally without considering any time zone information. If a time zone is embedded in the timestamp string, Greenplum Database 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.

From To Description
BIGINT TIMESTAMP9_LTZ Greenplum Database treats the BIGINT value as the number of nanoseconds started from ‘1970-01-01 00:00:00 +0000’.
DATE TIMESTAMP9_LTZ Greenplum Database 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/TIMESTAMP TIMESTAMP9_LTZ Greenplum Database 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/TIMESTAMPTZ TIMESTAMP9_LTZ For this conversion, Greenplum Database only extends the fractional part to nanosecond precision.
TIMESTAMP9_LTZ BIGINT The 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_LTZ DATE The 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_LTZ TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP The 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_LTZ TIMESTAMP WITH TIME ZONE/TIMESTAMP When performing this conversion, Greenplum Database truncates the fractional part to only 6 digits.
BIGINT TIMESTAMP9_NTZ When performing this conversion, Greenplum Database treats the BIGINT value as the number of nanoseconds started from ‘1970-01-01 00:00:00’.
DATE TIMESTAMP9_NTZ When performing this conversion, the resulting timestamp is ‘00:00:00.000000000’ on the given date.
TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP TIMESTAMP9_NTZ When peforming this conversion, Greenplum Database only extends the fractional part to nanosecond precision.
TIMESTAMP WITH TIME ZONE/TIMESTAMP TIMESTAMP9_NTZ The resulting timestamp’s value is determined by the value of TIMESTAMPTZ in the current session time zone.
TIMESTAMP9_NTZ BIGINT The 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_NTZ DATE When performing this conversion, Greenplum Database truncatest the time portion and preserves the date portion.
TIMESTAMP9_NTZ TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP When performing this conversion, Greenplum Database truncates only the fractional part to 6 digits.
TIMESTAMP9_NTZ TIMESTAMP WITH TIME ZONE/TIMESTAMP When performing this conversion, Greenplum Database only truncates the fractional part to 6 digits and add the time zone of the current session.
TIMESTAMP9_LTZ TIMESTAMP9_NTZ The resulting TIMESTAMP9_NTZ value is determined by the value of TIMESTAMP9_LTZ in the current session's time zone.
TIMESTAMP9_NTZ TIMESTAMP9_LTZ When performing this conversion, Greenplum Database 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 Greenplum Database uses when it prints a timestamp9 timestamp. When you set this parameter, Greenplum Database displays the timestamp value in that time zone. For example:

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)

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.