CODEWITHSUNDEEP
sqloracle-databasetimezone
Joe Anthony
Joe Anthony

Reputation: 5

Oracle SQL sysdate with time

I am using Oracle SQL. I'm trying to use sysdate with time to give the me query result. I have placed a time in the where clause, but I need it to be automatic and using the sysdate and converting to local time is the correct way. Any thoughts?

SELECT RESOURCE, AVG(SALES) AS SALES
FROM Z_HOURLY_RESOURCE
WHERE DATE_TIME_START BETWEEN to_date(to_char(FROM_TZ( CAST( (to_date('2018-08-02T05:00:00','yyyy-MM-dd"T"HH24:mi:ss') )AS TIMESTAMP ), 'America/Los_Angeles') AT TIME ZONE 'UTC', 'yyyy-MM-dd"T"HH24:mi:ss'),'yyyy-MM-dd"T"HH24:mi:ss')
  AND to_date(to_char(FROM_TZ( CAST( (to_date('2018-08-02T13:00:00','yyyy-MM-dd"T"HH24:mi:ss')+1) AS TIMESTAMP ), 'America/Los_Angeles') AT TIME ZONE 'UTC', 'yyyy-MM-dd"T"HH24:mi:ss'),'yyyy-MM-dd"T"HH24:mi:ss')

Upvotes: 0

Views: 2955

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59642

First of all you don't have to cast a TIMESTAMP to a CHAR and then back again to a TIMESTAMP.

Assuming DATE_TIME_START is a TIMESTAMP and times are given in UTC you can make it simpler. When Oracle compares TIMESTAMP WITH TIME ZONE values then comparison are always done automatically at UTC time value. Your condition would be like this.

SELECT RESOURCE, AVG(SALES) AS SALES
FROM Z_HOURLY_RESOURCE
WHERE FROM_TZ(DATE_TIME_START, 'UTC') 
   BETWEEN TO_TIMESTAMP_TZ('2018-08-02T05:00:00 America/Los_Angeles', 'yyyy-MM-dd"T"HH24:mi:ss TZR')
       AND TO_TIMESTAMP_TZ('2018-08-02T13:00:00 America/Los_Angeles', 'yyyy-MM-dd"T"HH24:mi:ss TZR')

However, due to function FROM_TZ(DATE_TIME_START, 'UTC') the performance might not be the best, it depends on your data.

If you need condition based on current time it would be like this:

SELECT RESOURCE, AVG(SALES) AS SALES
FROM Z_HOURLY_RESOURCE
WHERE FROM_TZ(DATE_TIME_START, 'UTC') 
   BETWEEN TRUNC(SYSTIMESTAMP) 
       AND TRUNC(SYSTIMESTAMP) + INTERVAL '1' DAY

Above query just illustrate time zone handling. You don't have to consider time zone of SYSTIMESTAMP, comparison will work in any case.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191570

It's slightly hard to work out quite what you need without sample data, but it sounds like you want to convert the time range 05:00 to 13:00 in your local session time zone (e.g. LA) to UTC to compare with the UTC-based timestamps in your table.

You can do that with:

WHERE DATE_TIME_START >= sys_extract_utc(cast(trunc(current_date) + 5/24 as timestamp with time zone))
  AND DATE_TIME_START <  sys_extract_utc(cast(trunc(current_date) + 13/24 as timestamp with time zone))

I've used >= and < rather than between on the assumption you really want up to 13:00, which is usually the case for time ranges. If you do want to include data form exactly 13:00:00 then change that < to <=, or go back to between.

TO explain what that is doing a but: current_date gives you the date/time in your session time zone. Truncating that sets the time to midnight (by default), so you can then add either 5 or 13 hours to get the times you want. That is still a date, so you can cast to timestamp with time zone so it represents that time in your session time zone again. You can then use sys_extract_utc() to get the UTC-equivalent timestamp.

To demonstrate those steps:

alter session set time_zone = 'America/Los_Angeles';
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF1';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF1 TZH:TZM';

select sysdate as a,
  current_date as b,
  trunc(current_date) as c,
  trunc(current_date) + 5/24 as d,
  cast(trunc(current_date) + 5/24 as timestamp with time zone) as e,
  sys_extract_utc(cast(trunc(current_date) + 5/24 as timestamp with time zone)) as f
from dual
union all
select sysdate as a,
  current_date as b,
  trunc(current_date) as c,
  trunc(current_date) + 13/24 as d,
  cast(trunc(current_date) + 13/24 as timestamp with time zone) as e,
  sys_extract_utc(cast(trunc(current_date) + 13/24 as timestamp with time zone)) as f
from dual;

A                   B                   C                   D                   E                            F                    
------------------- ------------------- ------------------- ------------------- ---------------------------- ---------------------
2018-08-02 18:56:23 2018-08-02 10:56:23 2018-08-02 00:00:00 2018-08-02 05:00:00 2018-08-02 05:00:00.0 -07:00 2018-08-02 12:00:00.0
2018-08-02 18:56:23 2018-08-02 10:56:23 2018-08-02 00:00:00 2018-08-02 13:00:00 2018-08-02 13:00:00.0 -07:00 2018-08-02 20:00:00.0

Upvotes: 3

Related Questions