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 |
+-------------------------------+

• tdavidson: I appreciate the assistance. Using both the second and third solutions you provided I am never receiving a ORA-30084 invalid data type for datatime primary with time zone modifier error.
• Wernfried Domscheit: For me all three statements are working. Check your code for typo
• tdavidson: Could it possibly be because the original column is a number data_type?
• Wernfried Domscheit: Which column should be number data type?
• tdavidson: The column I am converting from UTC is originally in a number datatype and the end goal is to convert from number datatype (unix timestamp) to UTC datetime, then to PST datetime.