Timezone Date Format in Oracle

Timezone date format in Oracle

That will give you ISO-8601 mentioned in comment:

select to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:sstzh:tzm') isodt from dual;

If you really want Z instead of timezone you can use:

    select to_char(cast(systimestamp as timestamp) at time zone 'UTC',
               'yyyy-mm-dd"T"hh24:mi:ss"Z"')
    from dual;

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.

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.

Oracle SQL how to convert time zone string to date

This should work:

SELECT TO_DATE(SUBSTR('2015-06-17T00:00:00.000+05:00',1,10),'yyyy-mm-dd') from dual

---

Related Topics