Oracle Convert Timestamp with Timezone to Date

Oracle how to convert Timestamp with any Timezone, to Date with database server Timezone

You don't need to convert the table data; as well as being more work, doing so would stop any index on that column being used.

Oracle will honour time zones when comparing values, so compare the original table data with the specific day - and convert that to a timestamp with time zone:

select * 
from MY_TABLE 
where MY_TIMESTAMP >= timestamp '2019-03-19 00:00:00 Europe/London'
and   MY_TIMESTAMP <  timestamp '2019-03-20 00:00:00 Europe/London'

or if you want to base it on today rather than a fixed date:

where MY_TIMESTAMP >= from_tz(cast(trunc(sysdate) as timestamp), 'Europe/London')
and   MY_TIMESTAMP <  from_tz(cast(trunc(sysdate) + 1 as timestamp), 'Europe/London')

or if you're being passed the dates as YYYYDDD values (replace fixed value with numeric argument name):

where MY_TIMESTAMP >= from_tz(to_timestamp(to_char(2019078), 'RRRRDDD'), 'Europe/London')
and   MY_TIMESTAMP <  from_tz(to_timestamp(to_char(2019079), 'RRRRDDD'), 'Europe/London')

Quick demo with some sample data in a CTE, in two zones for simplicity:

with my_table (id, my_timestamp) as (
  select 1, timestamp '2019-03-19 00:37:56.030000000 Europe/Paris' from dual
  union all
  select 2, timestamp '2019-03-19 00:37:56.030000000 Europe/London' from dual
  union all
  select 3, timestamp '2019-03-19 01:00:00.000000000 Europe/Paris' from dual
  union all
  select 4, timestamp '2019-03-20 00:37:56.030000000 Europe/Paris' from dual
  union all
  select 5, timestamp '2019-03-20 00:37:56.030000000 Europe/London' from dual
)

select * 
from MY_TABLE 
where MY_TIMESTAMP >= timestamp '2019-03-19 00:00:00 Europe/London'
and   MY_TIMESTAMP <  timestamp '2019-03-20 00:00:00 Europe/London'
/

        ID MY_TIMESTAMP                                      
---------- --------------------------------------------------
         2 2019-03-19 00:37:56.030000000 EUROPE/LONDON       
         3 2019-03-19 01:00:00.000000000 EUROPE/PARIS        
         4 2019-03-20 00:37:56.030000000 EUROPE/PARIS        

The first sample row is excluded because 00:37 in Paris is still the previous day in London. The second and third are included because they are both in the early hours of that day - the third row just scrapes in. The fourth row is included for the same reason the first was excluded - 00:37 tomorrow is still today from London. And the fifth is excluded because it's after midnight in London.

How to convert ORACLE date and timestamp types to string with timezone?

Assuming the date or timestamp already represents a time in UTC and doesn't need to be converted from another time zone, you just need to_char:

select to_char(timestamp '2020-12-31 21:00:00', 'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z")
from dual

2020-12-31T21:00:00.000Z

The "T" and "Z" are character literals.

If it's a date the .000 part can be treated as a literal too, since dates don't have fractional seconds and FF isn't valid for a date:

select to_char(cast(timestamp '2020-12-31 21:00:00' as date), 'YYYY-MM-DD"T"HH24:MI:SS".000Z"')
from dual

2020-12-31T21:00:00.000Z

Or cast the date to a timestamp if you prefer.

If you do need to adjust the time zone then you can do that before converting to a string.

db<>fiddle

ORACLE SQL adjust date for timezone without casting to timestamp or char

It seems that you need something like this (which would be a lot easier if Oracle also had "date with time stamp" data types):

...
cast(from_tz(cast(your_date as timestamp), 'UTC') at time zone 'US/Eastern'
  as date) as your_column_alias
...

Cast the date as timestamp, so you can give it a time zone (UTC is the new GMT - that's a separate issue), convert to your required time zone, and then convert back to date - no time zone, no fractional seconds.

Converting a timestamp with Time zone to just a timestamp

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 tome 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_tc():

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

---

Related Topics