CODEWITHSUNDEEP
oracle-database
johanrex
johanrex

Reputation: 419

Get UTC time from DATE column in Oracle

I'm trying to figure out when a job is running and want to display the time in UTC time.

This gives the server time (I assume):

select 
  to_char(last_date, 'yyyy-mm-dd hh24:mi:ss') last
from all_jobs;

But I'd like to display it as UTC time in order to compare between different servers. I've tried using SYS_EXTRACT_UTC like this but it expects a timestamp instead:

select 
  SYS_EXTRACT_UTC(to_char(last_date, 'yyyy-mm-dd hh24:mi:ss')) last
from all_jobs;

ORA-30175: invalid type given for an argument

Upvotes: 3

Views: 2627

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59476

By definition a DATE value has no time zone information, thus you cannot extract any UTC time (or any other time zone value).

Only solution is to tell Oracle explicitly the time zone of your value, then you can convert to different timezone, for example like this:

SELECT SYS_EXTRACT_UTC(
    TO_TIMESTAMP(last_date) AT TIME ZONE 'Europe/Zurich'
    )
FROM all_jobs;

Upvotes: 6

Related Questions