'Oracle Convert TIMESTAMP with Timezone to DATE

I have DB with timezone +04:00 (Europe/Moscow) and need to convert a string in format YYYY-MM-DD"T"HH24:MI:SSTZH:TZM to DATE data type in Oracle 11g.

In other words, I have a string 2013-11-08T10:11:31+02:00 and I want to convert it to DATE data type (in local DB timezone +04:00 (Europe/Moscow)).

For string 2013-11-08T10:11:31+02:00 my desired transformation should return DATE data type with date 2013-11-08 12:11:31 (i.e. with local timezone transformation of time to +04:00 (Europe/Moscow)). Timezone of string may be different and +02:00 in string above is just example.

I tried to do this with TIMESTAMP data type, but no success with time zone transformation.

sqloracledatetimestamp


Solution 1:[1]

to_timestamp_tz() function with at time zone clause can be used to convert your string literal to a value of timestamp with time zone data type: 

SQL> with t1(tm) as(
  2    select '2013-11-08T10:11:31+02:00' from dual
  3  )
  4  select to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM')
  5           at time zone '+4:00'         as this_way
  6       , to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM')
  7           at time zone 'Europe/Moscow' as or_this_way
  8    from t1
  9  /

Result:

THIS_WAY                            OR_THIS_WAY
----------------------------------------------------------------------------
2013-11-08 12.11.31 PM +04:00       2013-11-08 12.11.31 PM EUROPE/MOSCOW

And then, we use cast() function to produce a value of date data type:

with t1(tm) as(
  select '2013-11-08T10:11:31+02:00' from dual
)
select cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 
         at time zone '+4:00' as date)   as this_way  
     , cast(to_timestamp_tz(tm, 'yyyy-mm-dd"T"hh24:mi:ss TZH:TZM') 
         at time zone 'Europe/Moscow' as date) as or_this_way
  from t1

This_Way             Or_This_Way 
------------------------------------------
2013-11-08 12:11:31  2013-11-08 12:11:31

Find out more about at time zone clause and to_timestamp_tz() function.

Solution 2:[2]

SELECT
CAST((FROM_TZ(CAST(timezonefield AS TIMESTAMP),'GMT') AT TIME ZONE 'CET') AS DATE) 
FROM table;

Converts a timestamp in GMT to date in Central European time

Solution 3:[3]

if you want your timestamp with timezone to convert to a date in sync with "sysdate" then use the following:

select cast(to_timestamp_tz('2013-11-08T10:11:31-02:00'
                           ,'yyyy-mm-dd"T"hh24:mi:sstzh:tzm'
                           ) 
            at time zone to_char(systimestamp
                                ,'tzh:tzm'
                                ) 
            as date
           )
from dual

Solution 4:[4]

to cast time stamp to date :

cast(registrationmaster.Stamp5DateTime as date) >= '05-05-2018' AND cast(registrationmaster.Stamp5DateTime as date) <= '05-05-2018'

Solution 5:[5]

you can manage timezones with CAST(x AT TIME ZONE 'YYYY' AS DATE), this helps me:

WITH t1 (tm) AS (SELECT TIMESTAMP '2021-12-14 15:33:00 EET' FROM DUAL)

SELECT 'EET' tz, CAST (tm AT TIME ZONE 'Europe/Kaliningrad' AS DATE) AS datetime FROM t1
union SELECT 'MSK' tz, CAST (tm AT TIME ZONE 'Europe/Moscow' AS DATE) AS datetime FROM t1
union SELECT 'CET' tz, CAST (tm AT TIME ZONE 'Europe/Prague' AS DATE) AS datetime FROM t1
union SELECT 'UTC' tz, CAST (tm AT TIME ZONE 'UTC' AS DATE) AS datetime FROM t1

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2
Solution 3
Solution 4 Bhushan Shimpi
Solution 5

Related Questions