Convert column with unix timestamp from UTC to a different time zone

Question

Good afternoon,

I have a column within one of my tables that is stamped with a UNIX timestamp. I would like to have the return in my current timezone, PST. I have the following query and the below error is being returned. Any help would be greatly appreciated as I'm not sure if I am missing something simple, or way off base.

select TO_TIMESTAMP_TZ (('1970-01-01','YYYY-DD-MM;) + NUMTODSINTERVAL (table_name,'SECOND'),'UTC')) at time zone 'PST' 
from column_name

ORA-00907:missing right parenthesis
00907.00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line 16 Column:37. 

Answer 1

Your code has a few syntax issues, try one of these:

select (
    TIMESTAMP '1970-01-01 00:00:00 UTC' 
       + NUMTODSINTERVAL(column_name, 'SECOND')
    ) at time zone 'PST' 
from table_name


select (
   TO_TIMESTAMP_TZ('1970-01-01 UTC', 'YYYY-DD-MM TZR') 
      + NUMTODSINTERVAL(column_name,'SECOND')
   ) at time zone 'PST' 
from table_name

select (
   FROM_TZ(TO_TIMESTAMP('1970-01-01', 'YYYY-DD-MM'), 'UTC')    
      + NUMTODSINTERVAL(column_name,'SECOND')
   ) at time zone 'PST' 
from table_name

Be aware, PST is ambiguous. Better use IANA time zone names:

SELECT TZNAME, TZABBREV
FROM V$TIMEZONE_NAMES
WHERE TZABBREV = 'PST';

+-------------------------------+
|TZNAME                |TZABBREV|
+-------------------------------+
|America/Bahia_Banderas|PST     |
|America/Boise         |PST     |
|America/Creston       |PST     |
|America/Dawson        |PST     |
|America/Dawson_Creek  |PST     |
|America/Ensenada      |PST     |
|America/Hermosillo    |PST     |
|America/Inuvik        |PST     |
|America/Juneau        |PST     |
|America/Los_Angeles   |PST     |
|America/Mazatlan      |PST     |
|America/Metlakatla    |PST     |
|America/Santa_Isabel  |PST     |
|America/Sitka         |PST     |
|America/Tijuana       |PST     |
|America/Vancouver     |PST     |
|America/Whitehorse    |PST     |
|Canada/Pacific        |PST     |
|Canada/Yukon          |PST     |
|Mexico/BajaNorte      |PST     |
|Mexico/BajaSur        |PST     |
|Pacific/Pitcairn      |PST     |
|PST                   |PST     |
|PST8PDT               |PST     |
|US/Pacific            |PST     |
|US/Pacific-New        |PST     |
+-------------------------------+