Materialize Logo

Timestamp Data Types

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

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

For convenience and compatibility with PostgreSQL, timestamptz is accepted as an alias for timestamp with time zone.

Syntax

TIMESTAMP WITH TIME ZONE 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 timestamptz to:

To timestamp

You can cast the following types to timestamp or timestamptz:

Valid operations

timestamp data supports the following operations with other types.

Operation Computes
date + interval timestamp
date - interval timestamp
date + time timestamp
timestamp + interval timestamp
timestamp - interval timestamp
timestamp - timestamp 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