Materialize Logo

Timestamp Data Types

timestamp and timestamp with time zone data expresses a date and time in UTC.

timestamp info

Detail Info
Quick Syntax TIMESTAMP WITH TIME ZONE '2007-02-01 15:04:05+06'
Size 8 bytes
Catalog name pg_catalog.timestamp
OID 1083
Min value 4713 BC
Max value 294276 AD
Resolution 1 microsecond / 14 digits

timestamp with time zone info

Detail Info
Quick Syntax TIMESTAMPTZ '2007-02-01 15:04:05+06'
Aliases timestamp with time zone
Size 8 bytes
Catalog name pg_catalog.timestamptz
OID 1184
Min value 4713 BC
Max value 294276 AD
Resolution 1 microsecond / 14 digits

Syntax

TIMESTAMP TIMESTAMPTZ ' date_str T time_str + - tz_offset '
Field Use
WITH TIME ZONE Apply the tz_offset field. If not specified, don’t.
TIMESTAMPTZ Apply the tz_offset field.
date_str date_str
time_str A string representing a time of day in H:M:S.NS format.
tz_offset The timezone’s distance, in hours, from UTC.

Details

Valid casts

From timestamp

You can cast timestamp or timestamp with time zone to:

To timestamp

You can cast the following types to timestamp or timestamp with time zone:

Valid operations

timestamp and timestamp with time zone data (collectively referred to as timestamp/tz) supports the following operations with other types.

Operation Computes
date + interval timestamp/tz
date - interval timestamp/tz
date + time timestamp/tz
timestamp/tz + interval timestamp/tz
timestamp/tz - interval timestamp/tz
timestamp/tz - timestamp/tz interval

Examples

SELECT TIMESTAMP '2007-02-01 15:04:05' AS ts_v;
        ts_v
---------------------
 2007-02-01 15:04:05

SELECT TIMESTAMPTZ '2007-02-01 15:04:05+06' AS tstz_v;
         tstz_v
-------------------------
 2007-02-01 09:04:05 UTC
Did this info help?
Yes No