Oracle SQL: converting timestamp to UTC
Solution 1
select cast(mytimestamp as timestamp) at time zone 'UTC',
MyName,
MyBranch
from tableA
Because mytimestamp
is in fact a date
not a timestamp you need to cast it. Doing that makes Oracle assume that the information stored in mytimestamp
is in the time zone of the server -if that isn't the case you need to use Madhawas' solution.
Solution 2
Depending on the type, there are a couple of gotchas with regard to what time zone Oracle is converting from depending on what the data type of myTimestamp
is.
timestamp with time zone
It Just Works™. a_horse_with_no_name has the right answer here.
timestamp with local time zone
it is implicitly cast to timestamp with time zone, then It Just Works™. Again, a_horse_with_no_name is right here.
timestamp
While it too is implicitly cast to timestamp with time zone, the time zone that gets assigned by default is the session time zone (as opposed to the database time zone).
- The explicit invocation of this is
myTimestamp at local
. - Alternatively (and most likely better), you can do as Madhawas says and use the
from_tz
function to explicitly build a value with an explicit time zone other than that of your session.
date
Trying to do any of the above to date will fail as you described:
myTimestamp at time zone 'UTC'
ORA-30084: invalid data type for datetime primary with time zone modifierfrom_tz(myTimestamp, 'America/New_York')
ORA-00932: inconsistent datatypes: expected TIMESTAMP got DATE
The solution here is to cast the date to a timestamp first:
select from_tz(cast(myTimestamp as timestamp), 'America/New_York') from tableA
Sample Script
The following script illustrates the behavior. Note that on my system, dbtimezone
is US/Central, and sessiontimezone
is GMT-05:00.
I also use to_char
to convert the output as I have found some tools will alter the result timestamp in subtle ways, particularly if they don't have good timestamp support (this is rare nowadays, but still potentially a problem).
alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
/
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS'
/
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS TZR'
/
select dbtimezone
,sessiontimezone
,to_char(timestamp '2017-01-01 06:00:00') as ts
,to_char(timestamp '2017-01-01 06:00:00' at local) as ts_at_local
,to_char(timestamp '2017-01-01 06:00:00' at time zone dbtimezone) as ts_at_db
,to_char(timestamp '2017-01-01 06:00:00' at time zone sessiontimezone) as ts_at_session
from dual
/
The output on my system is as follows (reformatted as columnar for readability):
DBTIMEZONE US/Central
SESSIONTIMEZONE -05:00
TS 2017-01-01 06:00:00
TS_AT_LOCAL 2017-01-01 06:00:00 -05:00
TS_AT_DB 2017-01-01 05:00:00 US/CENTRAL
TS_AT_SESSION 2017-01-01 06:00:00 -05:00
Solution 3
You need to know your time zone for this;
SELECT myTimeStamp, from_tz(myTimeStamp, 'America/New_York') AT TIME ZONE 'UTC' utc FROM dual;
Related videos on Youtube
Fearghal
Updated on July 09, 2022Comments
-
Fearghal almost 2 years
I have a simple select query such as below but I noticed I am getting back the regional times. How can I convert to UTC in my select statment?
select myTimeStamp, MyName, MyBranch from tableA
Result: '27/03/2014 15:15:26' 'john', 'london'
I have tried using sys_extract_utc (myTimeStamp) but I have the error
sql command not properly ended
The column
myTimestamp
is of type 'date'.-
Alex Poole about 10 yearsIf your column is of type
date
, how do you know it has 'regional' times, and how would Oracle know what region/offset it represents - and so how to convert it to UTC? Do you have other information that identifies which region each value is supposed to be in?
-
-
Fearghal about 10 yearsThanks i tried this but i get 'ORA-300084 -Invalid data type for date time primary with time zone modifier'. I checked again and myTimestamp ios of type 'Date'.
-
Alex Poole about 10 yearsYou'd need to
cast(myTimeStamp as timestamp)
inside thefrom_tz
call though, since it's adate
. -
Fearghal about 10 yearsI dont know why i have a date type that has time in it but it does so my next question is...Will casting a date to dateTime overwrite the time part of my variable, resetting it to 00:00:00?
-
Alex Poole about 10 years@Fearghal - Oracle's
date
type has a time component so this is entirely normal. Casting totimestamp
will keep that time. (The fractional seconds part will be zero though). This is easy to test:select cast(sysdate as timestamp) from dual
. -
Fearghal about 10 yearsoh ok I see an issue, i think i missed described the issue. The solution described above will tie the date to the timezone. Now i need to do one more thing; change the time to be UTC? Eg if the time is 1am in PDT (UTC-7hrs) i want this time to be 08am UTC
-
Fearghal about 10 yearsThe solution described above will tie the date to the timezone. Now i need to do one more thing; change the time to be UTC? Eg if the time is 1am in PDT (UTC-7hrs) i want this time to be 08am UTC
-
Fearghal about 10 yearsThe solution described above will tie the date to the timezone. Now i need to do one more thing; change the time to be UTC? Eg if the time is 1am in PDT (UTC-7hrs) i want this time to be 08am UTC
-
Alex Poole about 10 years@Fearghal - this is adjusting the time. The
from_tz()
is tying the date to the time zone, EST/EDT in this example; the 'at time zone 'UTC'` is converting the time to UTC. As long as you know which time zone the date is originally from, this does what you said you need. This is too much code for a comment really, but:select from_tz(cast(to_date('2014-03-27 01:00', 'YYYY-MM-DD HH24:MI') as timestamp), 'America/Los_Angeles') at time zone 'UTC' from dual
gives27-MAR-14 08.00.00.000000 UTC
. If that isn't what you mean you'll need to expand your question. -
DDoomUs about 7 yearsI know this is completely old, but @cdonnellytx , do you have a documented reference for the following? While it too is implicitly cast to timestamp with time zone, the time zone that gets assigned by default is the session time zone (as opposed to the database time zone). I can't find docs that say "session" time zone is used (and that's not what I'm seeing in testing, I'm seeing UTC being used)
-
Chris R. Donnelly about 7 years@DDoomUs I don't have a documented reference, but I added a script that reproduces the behavior. Note on my system
dbtimezone
is US/Central andsessiontimezone
is-05:00
. Also, you may see different behavior in your client if you return the actual values. Older versions of Toad would alter the output date (12.x does not). -
DDoomUs about 7 years@ChrisR.Donnelly , Thanks for the response. Using SQL developer, regardless of the dbtimezone, or sessiontimezone, I see the exact same values for timezone data. Even a dump of the column data produces data which is date time components, entirely timezone independent. I've found that when I retrieve the data from an actual client, I'm getting the same thing, timezone independent data. (Just an fyi on what I'm seeing for you and anyone who may stumble across this in the future)