CODEWITHSUNDEEP
oracle-databasedateplsqlto-dateto-char
MoG
MoG

Reputation: 414

How to convert String with timezone to Date

How to convert below string to Date in Oracle

Wed Jan 13 23:01:24 GMT 2016

Tried below and this get's date as 

SELECT TO_CHAR(SYSDATE, 'DY MON DD HH24:MM:SS YYYY') FROM dual;

FRI AUG 26 14:08:04 2016

Eventually, Wanted something like this

SELECT TO_CHAR('Wed Jan 13 23:01:24 GMT 2016', 'DY MON DD HH24:MM:SS ??? YYYY') FROM dual;

Upvotes: 2

Views: 6483

Answers (2)

user3407335
user3407335

Reputation: 136

You need to use the TO_TIMESTAMP_TZ function.

The following example converts a character string to a value of TIMESTAMP WITH TIME ZONE:

SELECT TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

The -8:00 is the timezone.

https://docs.oracle.com/cd/B12037_01/server.101/b10759/functions179.htm#SQLRF06143

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

If the time zone is always a recognised abbreviation you can do:

select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

TO_TIMESTAMP_TZ('WEDJAN1323:01:24GMT2016','DYMONDDHH24:MI:SSTZDYYYY')
---------------------------------------------------------------------
2016-01-13 23:01:24 EUROPE/LONDON

You can't convert directly to a date because the time zone format elements aren't allowed in to_date(). If you had a fixed value - always GMT - you could ignore that by treating it as a literal, but you don't.

If you want it as a timestamp or a date, rather than a timestamp with time zone, you need to decide how to convert it. You can assume it's local time and essentially ignore the time zone by casting it, or you can adjust it to a specific time zone, e.g. UTC. There are various ways, here are a couple with a CTE to provide your sample and one in another zone (well, in summertime anyway so you get a different string):

with t (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
)
select to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as tstz,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(str, 'Dy Mon DD HH24:MI:SS TZD YYYY')) as tsutc
from t;

TSTZ                              TS                  DT                  TSUTC             
--------------------------------- ------------------- ------------------- -------------------
2016-01-13 23:01:24 EUROPE/LONDON 2016-01-13 23:01:24 2016-01-13 23:01:24 2016-01-13 23:01:24
2016-08-26 19:53:27 EUROPE/LONDON 2016-08-26 19:53:27 2016-08-26 19:53:27 2016-08-26 18:53:27

Exactly how you handle it depends on what you really need, of course.

Unfortunately that doesn't always work with abbreviations; Oracle doesn't necessarily recognise the values you see in Unix date command output, and the ones it does recognise aren't always available. Changing the session time zone can break it:

alter session set time_zone = 'America/Los_Angeles';
select to_timestamp_tz('Wed Jan 13 23:01:24 GMT 2016', 'Dy Mon DD HH24:MI:SS TZD YYYY')
from dual;

ORA-01857: not a valid time zone

You can change the session time zone to one that does recognise it (Europe/London) but that's a hack and won't work for all values anyway. It doesn't help that abbreviations can mean more than one thing.

If you have a list of known expected values and know what they really represent to you, you can swap the abbreviation for a region, but it doesn't really scale:

select to_timestamp_tz(
    replace(replace('Wed Jan 13 23:01:24 GMT 2016', 'GMT', 'Europe/London'),
      'BST', 'Europe/London'),
    'Dy Mon DD HH24:MI:SS TZR YYYY') from dual;

Or with multiple output formats:

with t1 (str) as (
  select 'Wed Jan 13 23:01:24 GMT 2016' from dual
  union all select 'Fri Aug 26 19:53:27 BST 2016' from dual
),
t2 (adj_str) as (
  select replace(replace(str, 'GMT', 'Europe/London'), 'BST', 'Europe/London')
  from t1
)
select to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as tstz,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as timestamp) as ts,
  cast(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY') as date) as dt,
  sys_extract_utc(to_timestamp_tz(adj_str, 'Dy Mon DD HH24:MI:SS TZR YYYY')) as tsutc
from t2;

You'd need to have nested replace calls (or regexp_replace to reduce the repetition a little) for each abbreviation you expect; or could have a function that hides that mess away from your main query.

Upvotes: 5

Related Questions