SQL UTC date instead of SYSDATE

Question

Working on the following query.

SELECT MIN(departure_date), ch_invoice.invoice_id
FROM ch_invoice
INNER JOIN ch_trip
ON ch_invoice.invoice_id = ch_trip.invoice_id
WHERE departure_date < SYSDATE
AND service_rendered = 0
AND paid = 1
Group By ch_invoice.invoice_id

Since the database that it is hitting may be in a location where the date hasn't changed yet, and we are using UTC as a standard for our dates, is it possible to replace SYSDATE with something like UTCDATE? I just need to know what day UTC is currently at.

Answer 1

As AntDC suggested, you can use the SYS_EXTRACT_UTC() function. That converts a 'datetime with time zone' from whatever time zone it is in to UTC.

SYSDATE doesn't have a time zone. If you passed that in then it would be implicitly converted to the system time zone, so it would work, but you can use SYSTIMESTAMP instead as that is already zone-aware.

You said in a comment that you want the date, but all Oracle dates have a time component; you can get the result as a date type with the time set to midnight (which makes sense for the comparison you're doing) with TRUNC():

select systimestamp,
  sys_extract_utc(systimestamp) as utctime,
  trunc(sys_extract_utc(systimestamp)) as utcdate
from dual;

SYSTIMESTAMP                     UTCTIME                   UTCDATE
-------------------------------- ------------------------- -------------------
2016-04-08 17:18:27.352 +01:00   2016-04-08 16:18:27.352   2016-04-08 00:00:00

That's running on a server in the UK, so the local time is BST.

Since that doesn't involve a day change, the effect can be demonstrated using the session time instead of the server time:

alter session set time_zone = '+12:00';

select current_timestamp,
  sys_extract_utc(current_timestamp) as utctime,
  trunc(sys_extract_utc(current_timestamp)) as utcdate
from dual;

CURRENT_TIMESTAMP                UTCTIME                   UTCDATE
-------------------------------- ------------------------- -------------------
2016-04-09 04:23:17.910 +12:00   2016-04-08 16:23:17.910   2016-04-08 00:00:00

My session time is 2016-04-09, but it still finds the UTC date as 2016-04-08.

(To be clear, I am not suggesting you switch to using current_timestamp or current_date; I'm using those instead of systimestamp and sysdate purely as a demo).