Convert a date from UTC timezone to PST Time zone (including DST factor)

I want to convert a date in UTC timezone to PST timezone. The NEW_TIME doesn't take into account the DST (Daylight Saving Time) factor, so I tried using CAST and TO_TIMESTAMP_TZ functions but both gave incorrect time ( difference of around 5 hrs 30 min). Not sure why.

SELECT TO_CHAR(TO_TIMESTAMP_TZ(max(end_date) AT TIME ZONE 'PST')
      ,'DD-MON-YYYY HH24:MI:SS')
FROM table1
WHERE NAME= 'FIRST';

SELECT TO_CHAR(CAST((max(end_date) AT TIME ZONE 'PST') AS DATE )
      ,'DD-MON-YYYY HH24:MI:SS')
FROM table1
WHERE NAME= 'FIRST';

Instead of 'PST' I also tried using 'US/PACIFIC', but it too gave the same result.

max(end_date) is : 2021-03-15 07:17:16 (in UTC)

The query is returning (incorrect time returned) : 14-MAR-2021 18:47:16

The time that it should (correct time expected): 15-MAR-2021 00:17:16

Can anyone please help in correcting my query or any other function that can convert the date from UTC to PST time zone (keeping the DST factor in mind).

You have 6 hours time difference, so I'm going to assume that you are in the same time zone as Asia/Dacca and have set up my session using:

ALTER SESSION SET TIME_ZONE='Asia/Dacca';

Now, if I create table1 with the data type TIMESTAMP WITH TIME ZONE:

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date TIMESTAMP WITH TIME ZONE
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16 UTC' );

Then your query (you do not need to use TO_TIMESTAMP_TZ on a column that is already a TIMESTAMP WITH TIME ZONE column):

SELECT TO_CHAR(
         max(end_date) AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Outputs:

| PST_END_DATE         |
| :------------------- |
| 15-MAR-2021 00:17:16 |

and works!

---

However, if you store end_date using a TIMESTAMP (without time zone):

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date TIMESTAMP
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16' );

Then:

SELECT TO_CHAR(
         max(end_date) AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Outputs:

| PST_END_DATE         |
| :------------------- |
| 14-MAR-2021 18:17:16 |

Which replicates your issue.

This is because the database does not know the time zone of the data and will implicitly assume that it is the same as the database/session time zone and we've set that to Asia/Dacca and not UTC. Instead we need to explicitly tell the database to use the UTC time zone for the conversion:

SELECT TO_CHAR(
         FROM_TZ(max(end_date), 'UTC') AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

Which outputs:

| PST_END_DATE         |
| :------------------- |
| 15-MAR-2021 00:17:16 |

---

If your column has the DATE data type:

CREATE TABLE table1 (
  name     VARCHAR2(20),
  end_date DATE
);

INSERT INTO table1 ( name, end_date ) VALUES ( 'FIRST', TIMESTAMP '2021-03-15 07:17:16' );

Then you can use the same query with an added CAST:

SELECT TO_CHAR(
         FROM_TZ(CAST(max(end_date) AS TIMESTAMP), 'UTC') AT TIME ZONE 'PST',
         'DD-MON-YYYY HH24:MI:SS'
       ) AS pst_end_date
FROM   table1
WHERE  NAME = 'FIRST';

db<>fiddle here