This documentation is related to an older version of Firebolt. For the most current documentation, see Firebolt documentation.
TO_TIMESTAMPTZ
You are looking at the documentation for Firebolt’s redesigned date and timestamp types. These types were introduced in DB version 3.19 under the names
PGDATE,TIMESTAMPNTZandTIMESTAMPTZ, and synonymsDATE,TIMESTAMPandTIMESTAMPTZmade available in DB version 3.22.If you worked with Firebolt before DB version 3.22, you might still be using the legacy date and timestamp types. Determine which types you are using by executing the query
SELECT EXTRACT(CENTURY FROM DATE '2023-03-16');. If this query returns a result, you are using the redesigned date and timestamp types and can continue with this documentation. If this query returns an error, you are using the legacy date and timestamp types and can find legacy documentation here, or instructions to use the new types here.
Converts the number of seconds since the Unix epoch to a TIMESTAMPTZ value.
Syntax
TO_TIMESTAMPTZ(<value>)
Parameters
| Parameter | Description | Supported input types |
|---|---|---|
<value> | A numeric expression to convert. The number left of the decimal separator is interpreted as the number of seconds before or after the Unix epoch 1970-01-01 00:00:00 UTC. The fractional part (if present) is interpreted as subseconds. | INTEGER, BIGINT, NUMERIC, DOUBLE PRECISION |
Return Type
TIMESTAMPTZ
Remarks
TO_TIMESTAMPTZ is the inverse function of EXTRACT(EPOCH FROM TIMESTAMPTZ).
Example
The following example assumes that the current Unix timestamp is 2023-03-03 14:42:31.123456 UTC.
SELECT TO_TIMESTAMPTZ(EXTRACT(EPOCH FROM CURRENT_TIMESTAMPTZ)) = CURRENT_TIMESTAMPTZ; --> true
SET time_zone = 'Europe/Berlin';
SELECT TO_TIMESTAMPTZ(42::INTEGER); --> 1970-01-01 01:00:42+01
SELECT TO_TIMESTAMPTZ(-9876543210::BIGINT); --> 1657-01-09 04:39:58+00:53:28
SELECT TO_TIMESTAMPTZ(42.123456::DOUBLE PRECISION); --> 1970-01-01 01:00:42.123456+01
SELECT TO_TIMESTAMPTZ(42.123456::NUMERIC(38, 9)); --> 1970-01-01 01:00:42.123456+01