CODEWITHSUNDEEP
sqloracle-database
John Wick
John Wick

Reputation: 745

Converting a timestamp with Time zone to just a timestamp

Trying to insert some data into a a table. However, I tried i am receiving data compatibility issues. The column that I am trying to insert into is a timestamp(6) column while the column I am drawing data from is a timestamp with timezone column. I know how to use cast to convert from timestamp to a timestamp with timezone but not the inverse. Is there a way I can just strip out the 'UTC'?

Date I am starting out with: 

'20-MAY-18 09.00.00.000000000 AM UTC'

Date I want to end up with:

'20-MAY-18 09.00.00.000000000 AM'

What I have tried thus far: 

select to_date('20-MAY-18 09.00.00.000000000 AM UTC', 'dd-mon-yy hh:mi:ss A.M.') from dual;

However I receive an error, and I just can't seem to figure it out, what am I doing wrong? thanks in advance!

Upvotes: 0

Views: 7358

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

You can cast a timestamp with time zone to a plain timestamp:

cast(<your_value> as timestamp)

so with your value:

select cast(
  to_timestamp_tz('20-MAY-18 09.00.00.000000000 AM UTC', 'DD-MON-RR HH:MI:SS.FF AM TZR')
  as timestamp)
from dual;

CAST(TO_TIMESTAMP_T
-------------------
2018-05-20 09:00:00

If you insert as timestamp with time zone value into a plain timestamp column then it will be converted automatically, just losing its tie zone information.

If the values might not always be UTC then you can convert them to UTC and to a plain timestamp in one go with sys_extract_utc():

with cte (tsz) as (
            select timestamp '2018-05-20 09:00:00.0 UTC' from dual
  union all select timestamp '2018-05-20 13:00:00.0 America/New_York' from dual
)
select tsz, cast(tsz as timestamp) as ts, sys_extract_utc(tsz) utc
from cte;

TSZ                            TS                  UTC                
------------------------------ ------------------- -------------------
2018-05-20 09:00:00.000 +00:00 2018-05-20 09:00:00 2018-05-20 09:00:00
2018-05-20 13:00:00.000 -04:00 2018-05-20 13:00:00 2018-05-20 17:00:00

Upvotes: 4

Related Questions