1

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. 

1 Answer 1

0

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     |
+-------------------------------+
6
  • 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.
    – tdavidson
    Apr 21, 2023 at 14:47
  • For me all three statements are working. Check your code for typo Apr 21, 2023 at 14:59
  • Could it possibly be because the original column is a number data_type?
    – tdavidson
    Apr 21, 2023 at 15:21
  • Which column should be number data type? Apr 21, 2023 at 15:22
  • 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.
    – tdavidson
    Apr 21, 2023 at 15:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.