The TIMESTAMP
and TIMESTAMPTZ
data types stores a date and time pair in UTC.
Time Zone Details
TIMESTAMP
has two variants:
TIMESTAMP WITH TIME ZONE
convertsTIMESTAMP
values from UTC to the client's session time zone (unless another time zone is specified for the value). However, it is conceptually important to note thatTIMESTAMP WITH TIME ZONE
does not store any time zone data.
TIMESTAMP WITH TIME ZONE
values display in UTC.TIMESTAMP WITHOUT TIME ZONE
presents allTIMESTAMP
values in UTC.
The difference between these two types is that TIMESTAMP WITH TIME ZONE
uses the client's session time zone, while the other simply does not. This behavior extends to functions like now()
and extract()
on TIMESTAMP WITH TIME ZONE
values.
Best Practices
We recommend always using the ...WITH TIME ZONE
variant because the ...WITHOUT TIME ZONE
variant can sometimes lead to unexpected behaviors when it ignores a session offset.
However, we also recommend you avoid setting a session time for your database. Instead, convert values from the default time zone (UTC) on the client side.
Aliases
In CockroachDB, the following are aliases:
TIMESTAMP
,TIMESTAMP WITHOUT TIME ZONE
TIMESTAMPTZ
,TIMESTAMP WITH TIME ZONE
Syntax
A constant value of type TIMESTAMP
/TIMESTAMPTZ
can be expressed using an
interpreted literal, or a
string literal
annotated with
type TIMESTAMP
/TIMESTAMPTZ
or
coerced to type
TIMESTAMP
/TIMESTAMPTZ
.
TIMESTAMP
constants can be expressed using the
following string literal formats:
Format | Example |
---|---|
Date only | TIMESTAMP '2016-01-25' |
Date and Time | TIMESTAMP '2016-01-25 10:10:10.555555' |
ISO 8601 | TIMESTAMP '2016-01-25T10:10:10.555555' |
To express a TIMESTAMPTZ
value (with time zone offset from UTC), use
the following format: TIMESTAMPTZ '2016-01-25 10:10:10.555555-05:00'
When it is unambiguous, a simple unannotated string literal can also
be automatically interpreted as type TIMESTAMP
or TIMESTAMPTZ
.
Note that the fractional portion is optional and is rounded to microseconds (6 digits after decimal) for compatibility with the PostgreSQL wire protocol.
Size
A TIMESTAMP
column supports values up to 12 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata.
Examples
> CREATE TABLE timestamps (a INT PRIMARY KEY, b TIMESTAMPTZ);
> SHOW COLUMNS FROM timestamps;
+-------+--------------------------+-------+---------+
| Field | Type | Null | Default |
+-------+--------------------------+-------+---------+
| a | INT | false | NULL |
| b | TIMESTAMP WITH TIME ZONE | true | NULL |
+-------+--------------------------+-------+---------+
(2 rows)
> INSERT INTO timestamps VALUES (1, TIMESTAMPTZ '2016-03-26 10:10:10-05:00'), (2, TIMESTAMPTZ '2016-03-26');
> SELECT * FROM timestamps;
+---+---------------------------+
| a | b |
+---+---------------------------+
| 1 | 2016-03-26 15:10:10+00:00 |
| 2 | 2016-03-26 00:00:00+00:00 |
+---+---------------------------+
# Note that the first timestamp is UTC-05:00, which is the equivalent of EST.
Supported Casting & Conversion
TIMESTAMP
values can be cast to any of the following data types:
Type | Details |
---|---|
INT |
Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice. |
SERIAL |
Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice. |
DECIMAL |
Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice. |
FLOAT |
Converts to number of seconds since the Unix epoch (Jan. 1, 1970). This is a CockroachDB experimental feature which may be changed without notice. |
DATE |
–– |
STRING |
–– |
SERIAL
data type represents values automatically generated by CockroachDB to uniquely identify rows, you cannot meaningfully cast other data types as SERIAL
values.