How to convert sysdate to UTC time in oracle

sql oracle datetime sysdate
Shubham Khatri picture Shubham Khatri · Oct 18, 2016 · Viewed 10.3k times · Source

I am having a query to subtract a date from the sysdate. However the date object I have is in UTC but sysdate doesn't give me UTC time. How to I convert sysdate to give me date in UTC.

I have already tried using sys_extract_utc

select sys_extract_utc(systimestamp) from dual;

This works fine and gives me the correct UTC time stamp. But the object returned by it is not the same as sysdate so when I subtract date from this object I get an INTERVAL DAY to SECOND but it should be a number.

Answer

Shubham Khatri picture Shubham Khatri · Oct 18, 2016

Using CAST on the result of sys_extract_utc(systimestamp) is the solution to this since sys_extract_utc returns timestamp.

select cast(sys_extract_utc(systimestamp) as DATE) from dual;

Related questions

SQL TRUNC Minutes from Sysdate

I am currently using one of the columns to extract every minute of the time, however, it would skip some minutes because no data was inserted that minute. Output Below Expected. 12-19-2017 02:23:00 12-19-2017 02:24:00 12-19-2017 02:25:00 12-19-2017 02:26:00 12-19…

Read More
How to insert date values into table

How can I insert into table with different input using / ,with date datatype? insert into run(id,name,dob)values(&id,'&name',[what should I write here?]); I'm using oracle 10g.

Read More
In Oracle SQL: How do you insert the current date + time into a table?

I've written below code, but it only seems to insert the current date and not the current time. Anyone knows how to do that? insert into errortable (dateupdated,table1id) values (TO_DATE(sysdate, 'dd/mm/yyyy hh24:mi:ss'),1083);

Read More