timestamp9

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:

  1. 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 TypeStorage SizeDescriptionMax ValueMin ValueResolution
TIMESTAMP98 bytesLike TIMESTAMP9_LTZ. Timestamp with local time zone.2261-12-31 23:59:59.999999999 +00001700-01-01 00:00:00.000000000 +00001 nanosecond
TIMESTAMP9_LTZ8 bytesTimestamp with local time zone.2261-12-31 23:59:59.999999999 +00001700-01-01 00:00:00.000000000 +00001 nanosecond
TIMESTAMP9_NTZ8 bytesTimestamp without time zone.2261-12-31 23:59:59.999999999 +00001700-01-01 00:00:00.000000000 +00001 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.

FromToDescription
BIGINTTIMESTAMP9_LTZGreenplum Database treats the BIGINT value as the number of nanoseconds started from ‘1970-01-01 00:00:00 +0000’.
DATETIMESTAMP9_LTZGreenplum 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/TIMESTAMPTIMESTAMP9_LTZGreenplum 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/TIMESTAMPTZTIMESTAMP9_LTZFor this conversion, Greenplum Database 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, Greenplum Database truncates the fractional part to only 6 digits.
BIGINTTIMESTAMP9_NTZWhen performing this conversion, Greenplum Database 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, Greenplum Database 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, Greenplum Database truncatest the time portion and preserves the date portion.
TIMESTAMP9_NTZTIMESTAMP WITHOUT TIME ZONE/TIMESTAMPWhen performing this conversion, Greenplum Database truncates only the fractional part to 6 digits.
TIMESTAMP9_NTZTIMESTAMP WITH TIME ZONE/TIMESTAMPWhen performing this conversion, Greenplum Database 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, Greenplum Database adds the timezone of the current sesion.

Type Conversion Examples

Convert BIGINT to TIMESTAMP9_LTZ

  1. =# SHOW TIMEZONE; 
  3.    TimeZone 
  4. -------------- 
  5. Asia/Shanghai 
  6. (1 row) 
  8. =# SELECT 0::BIGINT::TIMESTAMP9_LTZ; 
  10.            timestamp9_ltz 
  11. ------------------------------------ 
  12. 1970-01-01 08:00:00.000000000 +0800 
  13. (1 row)

Convert DATE to TIMESTAMP9_LTZ

  1. =# SHOW TIMEZONE; 
  3.    TimeZone 
  4. -------------- 
  5. Asia/Shanghai 
  6. (1 row) 
  8.  =# SELECT '2023-01-01'::DATE::TIMESTAMP9_LTZ; 
  10.            timestamp9_ltz 
  11. ------------------------------------- 
  12. 2023-01-01 00:00:00.000000000 +0800 
  13. (1 row) 
  15. =# SELECT '2023-01-01'::DATE::TIMESTAMPTZ; 
  17.       timestamptz 
  18. ------------------------ 
  19. 2023-01-01 00:00:00+08 
  20. (1 row)

Convert TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP to TIMESTAMP9_LTZ

  1. =# SHOW TIMEZONE; 
  3.    TimeZone 
  4. --------------- 
  5. Asia/Shanghai 
  6. (1 row) 
  8. Time: 0.411 ms 
  9. =# SELECT '2023-01-01 00:00:00'::TIMESTAMP::TIMESTAMP9_LTZ; 
  11.            timestamp9_ltz 
  12. ------------------------------------- 
  13. 2023-01-01 00:00:00.000000000 +0800 
  14. (1 row) 
  16. Time: 0.691 ms 
  17. =# SELECT '2023-01-01 00:00:00'::TIMESTAMP::TIMESTAMPTZ; 
  19.       timestamptz 
  20. ------------------------ 
  21. 2023-01-01 00:00:00+08 
  22. (1 row)

Convert TIMESTAMP WITH TIME ZONE/TIMESTAMP to TIMESTAMP9_LTZ

  1. =# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMPTZ::TIMESTAMP9_LTZ; 
  3.            timestamp9_ltz 
  4. ------------------------------------- 
  5. 2023-01-01 00:00:00.123456000 +0800 
  6. (1 row)

Convert TIMESTAMP9_LTZ to BIGINT

  1. =# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMP9_LTZ::BIGINT; 
  3.         int8 
  4. --------------------- 
  5. 1672502400123456000 
  6. (1 row) 
  8. =# SELECT '1969-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMP9_LTZ::BIGINT; 
  10.         int8 
  11. -------------------- 
  12. -31564799876544000 
  13. (1 row)

Convert TIMESTAMP9_LTZ to DATE

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMPTZ::DATE; 
  4.     date 
  5. ------------ 
  6. 2023-01-02 
  7. (1 row) 
  9. =# SET TIMEZONE TO 'UTC+0'; 
  10. SET 
  11. =# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMPTZ::DATE; 
  12.     date 
  13. ------------ 
  14. 2023-01-01 
  15. (1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP

Example 1

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 
  5.       timestamp 
  6. --------------------- 
  7. 2023-01-02 02:59:59 
  8. (1 row) 
  10. =# SET TIMEZONE TO 'UTC+0'; 
  12. SET 
  13. =# SELECT '2023-01-02 02:59:59 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 
  15.       timestamp 
  16. --------------------- 
  17. 2023-01-01 18:59:59 
  18. (1 row)

Example 2 — Truncation of the fractional part

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-02 02:59:59.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP; 
  5.          timestamp 
  6. ---------------------------- 
  7. 2023-01-02 02:59:59.123456 
  8. (1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP WITH TIME ZONE/TIMESTAMP

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

Convert BIGINT to TIMESTAMP9_NTZ

  1. =# SELECT 0::BIGINT::TIMESTAMP9_NTZ; 
  2.         timestamp9_ntz 
  3. ------------------------------- 
  5. 1970-01-01 00:00:00.000000000 
  6. (1 row)

Convert DATE to TIMESTAMP9_NTZ

  1. =# SELECT '2023-01-01'::DATE::TIMESTAMP9_NTZ; 
  2.         timestamp9_ntz 
  3. ------------------------------- 
  4. 2023-01-01 00:00:00.000000000 
  5. (1 row)

Convert TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP to TIMESTAMP9_NTZ

  1. =# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP::TIMESTAMP9_NTZ; 
  2.         timestamp9_ntz 
  4. ------------------------------ 
  5. 2023-01-01 00:00:00.123456000 
  6. (1 row)

Convert TIMESTAMP WITH TIME ZONE/TIMESTAMP to TIMESTAMP9_NTZ

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMPTZ::TIMESTAMP9_NTZ; 
  4.         timestamp9_ntz 
  5. ------------------------------- 
  6. 2023-01-01 00:00:00.123456000 
  7. (1 row) 
  9. =# SET TIMEZONE TO 'UTC+0'; 
  10. SET 
  11. =# SELECT '2023-01-01 00:00:00.123456 Asia/Shanghai'::TIMESTAMPTZ::TIMESTAMP9_NTZ; 
  12.         timestamp9_ntz 
  13. ------------------------------- 
  14. 2022-12-31 16:00:00.123456000 
  15. (1 row)

Convert TIMESTAMP9_NTZ to BIGINT

  1. =# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::BIGINT; 
  2.         int8 
  3. --------------------- 
  4. 1672531200123456000 
  5. (1 row) 
  7. =# SELECT '1969-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::BIGINT; 
  8.         int8 
  9. -------------------- 
  10. -31535999876544000 
  11. (1 row)

Convert TIMESTAMP9_NTZ to DATE

  1. =# SELECT '2023-01-01 00:00:00.123456'::TIMESTAMP9_NTZ::DATE; 
  2.     date 
  3. ------------ 
  4. 2023-01-01 
  5. (1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP WITHOUT TIME ZONE/TIMESTAMP

  1. =# SELECT '2023-01-01 00:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP; 
  2.          timestamp 
  3. ---------------------------- 
  4. 2023-01-01 00:00:00.123456 
  5. (1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP WITH TIME ZONE/TIMESTAMP

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMPTZ; 
  4.           timestamptz 
  5. ------------------------------- 
  6. 2023-01-01 23:00:00.123456+08 
  7. (1 row) 
  9. Time: 0.793 ms 
  10. =# SET TIMEZONE TO 'UTC+0'; 
  11. SET 
  12. =# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMPTZ; 
  13.           timestamptz 
  14. ------------------------------- 
  15. 2023-01-01 23:00:00.123456+00 
  16. (1 row)

Convert TIMESTAMP9_LTZ to TIMESTAMP9_NTZ

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-01 23:00:00.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP9_NTZ; 
  4.         timestamp9_ntz 
  5. ------------------------------- 
  6. 2023-01-01 23:00:00.123456789 
  7. (1 row) 
  9. =# SET TIMEZONE TO 'UTC+0'; 
  10. SET 
  11. =# SELECT '2023-01-01 23:00:00.123456789 Asia/Shanghai'::TIMESTAMP9_LTZ::TIMESTAMP9_NTZ; 
  12.         timestamp9_ntz 
  13. ------------------------------- 
  14. 2023-01-01 15:00:00.123456789 
  15. (1 row)

Convert TIMESTAMP9_NTZ to TIMESTAMP9_LTZ

  1. =# SET TIMEZONE TO 'Asia/Shanghai'; 
  2. SET 
  3. =# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP9_LTZ; 
  4.            timestamp9_ltz 
  5. ------------------------------------- 
  6. 2023-01-01 23:00:00.123456789 +0800 
  7. (1 row) 
  9. =# SET TIMEZONE TO 'UTC+0'; 
  10. SET 
  11. =# SELECT '2023-01-01 23:00:00.123456789'::TIMESTAMP9_NTZ::TIMESTAMP9_LTZ; 
  12.            timestamp9_ltz 
  13. ------------------------------------- 
  14. 2023-01-01 23:00:00.123456789 +0000 
  15. (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:

  1. testdb=# SELECT now()::timestamp9;
  2.                  now
  3. -------------------------------------
  4.  2022-08-24 18:08:01.729360000 +0800
  5. (1 row)
  7. testdb=# SET timezone TO 'UTC+2';
  8. SET
  9. testdb=# SELECT now()::timestamp9;
  10.                  now
  11. -------------------------------------
  12.  2022-08-24 08:08:12.995542000 -0200
  13. (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).

  1. The current system’s TIMEZONE parameter is ‘Asia/Shanghai’ 
  3. SELECT '2023-02-20 00:00:00.123456789 +0200'::TIMESTAMP9_LTZ; 
  5.            timestamp9_ltz 
  7. ------------------------------------- 
  8. 2023-02-20 06:00:00.123456789 +0800 
  9. (1 row) 
  11.  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. 
  13. SELECT '2023-02-20 00:00:00.123456789'::TIMESTAMP9_LTZ; 
  15.            timestamp9_ltz 
  16. ------------------------------------- 
  17. 2023-02-20 00:00:00.123456789 +0800 
  18. (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’.

  1. SELECT '123456789'::TIMESTAMP9_LTZ; 
  3.            timestamp9_ltz 
  4. ------------------------------------- 
  5. 1970-01-01 08:00:00.123456789 +0800 
  6. (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’

  1. =# SELECT '2023-02-20 00:00:00.123456789 +0200'::TIMESTAMP9_NTZ;
  2.         timestamp9_ntz
  3. -------------------------------
  4. 2023-02-20 00:00:00.123456789
  5. (1 row)
  6. =# SELECT '2023-02-20 00:00:00.123456789'::TIMESTAMP9_NTZ;
  7.         timestamp9_ntz
  8. -------------------------------
  9. 2023-02-20 00:00:00.123456789
  10. (1 row)

Limitations

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