How can I calculate the Unix time taking into account the time zone?

Question

Example:

SELECT
    SESSIONTIMEZONE,
    DT,
    TRUNC( (DT - date '1970-01-01') * 86400) AS EPOCH_DT
FROM "test_table"

outputs

SESSIONTIMEZONE	DT	EPOCH_DT
Europe/Paris	1970-01-02 00:00:00	86400

The desired output (tz_offset +01:00) would look like this:

SESSIONTIMEZONE	DT	EPOCH_DT
Europe/Paris	1970-01-02 00:00:00	82800

How could the following be changed so that the time zone is taken into account when calculating the Unix time?

TRUNC( (DT - date '1970-01-01') * 86400)

Answer 1

You can convert your date to a timestamp at a specific timezone (you appear to be wanting to use SESSIONTIMEZONE) and then you can convert that to a timestamp in the UTC time zone (using AT TIME ZONE 'UTC') and back to a date so you can find the difference from the epoch time:

SELECT SESSIONTIMEZONE,
       DT,
       (
         CAST(
           FROM_TZ(
             CAST(DT AS TIMESTAMP),
             SESSIONTIMEZONE
           ) AT TIME ZONE 'UTC'
           AS DATE
         )
         - DATE '1970-01-01'
       ) * 86400 AS EPOCH_DT
FROM   test_table

Which, for given the setup:

ALTER SESSION SET TIME_ZONE = 'Europe/Paris';

CREATE TABLE test_table (dt) AS
SELECT DATE '1970-01-02' FROM DUAL;

Outputs:

SESSIONTIMEZONE	DT	EPOCH_DT
Europe/Paris	1970-01-02 00:00:00	82800

---

However, it may make more sense to store your value as a TIMESTAMP WITH TIME ZONE so that the time zone is stored with the data rather than trying to assume it from the time zone of the local session (otherwise you will get different epoch values for the same query and same data depending on where in the world the database thinks people are from).

CREATE TABLE test_table2 (ts TIMESTAMP WITH TIME ZONE);
INSERT INTO test_table2 (ts) VALUES (TIMESTAMP '1970-01-02 00:00:00 Europe/Paris');

Then you can use:

SELECT ts,
       (CAST(ts AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01')
       * 86400 AS EPOCH_DT
FROM   test_table2

Which outputs:

TS	EPOCH_DT
1970-01-02 00:00:00.000000000 EUROPE/PARIS	82800

fiddle

---

If you want something that "works" for both DATE, TIMESTAMP and TIMESTAMP WITH TIME ZONE then:

SELECT SESSIONTIMEZONE,
       DT,
       ROUND((CAST(CAST(dt AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE 'UTC' AS DATE) - DATE '1970-01-01') * 86400, 6) AS EPOCH_DT
FROM   test_table

However, I still do not think it is a good idea to assume the time zone for DATE and TIMESTAMP data types based on the SESSIONTIMEZONE.

fiddle