CODEWITHSUNDEEP
sqloracle-databasetimezone
Noel
Noel

Reputation: 10525

Conversion of String with Abbreviated Timezone to Timestamp

I have a timestamp stored as varchar2 in May 29 14:12:56 PDT 2015 format. I want to convert this into timestamp with time zone data type.

If I use,

with x(dt_string) as (
  select 'May 29 14:12:56 PDT 2015' from dual
  )
select dt_string,
to_timestamp_tz(dt_string,'Mon dd hh24:mi:ss TZD YYYY') dt_ts
from x;

it gives me,

DT_STRING                 DT_TS
------------------------  ----------------------------------------
May 29 14:12:56 PDT 2015  29-MAY-15 02.12.56.000000000 PM +00:00

Time zone is incorrect.

Documentation says,

The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR.

Does this mean abbreviated time zone string should have Time zone region to perform proper conversion? But, having Time zone region will make abbreviated time zone string redundant. Isn't it?

How do I handle this?

Upvotes: 1

Views: 1661

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Actually your query should raise error ORA-01857: not a valid time zone or ORA-01882: timezone region not found

PDT is not a valid time zone region, i.e. it is ambiguous. Run this query to get different meanings of PDT:

SELECT tzabbrev, TZ_OFFSET(tzname), tzname
FROM v$timezone_names 
WHERE tzabbrev = 'PDT'
ORDER BY 2;

TZABBREV    TZ_OFFSET(TZNAME)   TZNAME
PDT -06:00  America/Inuvik
PDT -07:00  US/Pacific-New
PDT -07:00  America/Ensenada
PDT -07:00  America/Dawson
PDT -07:00  America/Dawson_Creek
PDT -07:00  America/Los_Angeles
PDT -07:00  America/Tijuana
PDT -07:00  America/Vancouver
PDT -07:00  America/Whitehorse
PDT -07:00  Canada/Pacific
PDT -07:00  Canada/Yukon
PDT -07:00  Mexico/BajaNorte
PDT -07:00  PST
PDT -07:00  PST8PDT
PDT -07:00  US/Pacific
PDT -08:00  America/Juneau

You have to use PST as time zone region. Daylight saving settings are then determined from given day:

SELECT 
    TO_TIMESTAMP_TZ('Jan 29 14:12:56 PST 2015','Mon dd hh24:mi:ss TZR YYYY') dt_ts_winter,
    TO_TIMESTAMP_TZ('Jun 29 14:12:56 PST 2015','Mon dd hh24:mi:ss TZR YYYY') dt_ts_summer 
FROM dual;

DT_TS_WINTER                            DT_TS_SUMMER
2015-01-29 14:12:56.000000000 -08:00    2015-06-29 14:12:56.000000000 -07:00

Since your values are stored as VARCHAR2 (now you know why you should not do it like this) you can change it with REGEXP_REPLACE(dt_string, 'PDT', 'PST')

Upvotes: 2

Related Questions