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

Question

I have a timestamp column, which contains data with different timezones. I need to select records which belongs to a given 'day' of database server timezone.

For example, if the data in MY_TIMESTAMP column is 19-MAR-19 00.37.56.030000000 EUROPE/PARIS.
And on given date 19-MAR-19 (also represented as 2019078) where the database server is on EUROPE/LONDON. Is there any way so that if my database server is in EUROPE/LONDON timezone, then this record is ignored but if it is in EUROPE/PARIS, then the record is selected.

Please note that the given timestamp is first hour of the day and EUROPE/PARIS is ahead of EUROPE/LONDON by one hour

The query I tried, unfortunately ignores the timezone of timestamp.

select * from MY_TABLE where to_number(to_char(CAST(MY_TIMESTAMP AS DATE), 'RRRRDDD')) between 2019078 AND 2019079

There is below mentioned way to convert TIMESTAMP from know timezone to a date in required timezone but I cannot use this logic as the source timezone is not know.

CAST((FROM_TZ(CAST(MY_TIMESTAMP AS TIMESTAMP),'EUROPE/PARIS') AT TIME ZONE 'EUROPE/LONDON') AS DATE)'

Answer 1

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.