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'.
This documentation for Oracle Database seems to say that TIMESTAMP WITH TIME ZONE type does record the incoming data's time zone or offset-from-UTC.
Oracle converts the data to a TIMESTAMP WITH LOCAL TIME ZONE value. This means the time zone that is entered ( -08:00 ) is converted to the session time zone value ( -07:00 ).
Oracle introduced TIMESTAMP data type in 9i version. It allows to store date time with fraction of seconds. By default format of TIMESTAMP is 'YYYY-MM-DD HH24:MI:SS. FF'.
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.
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.
It Just Works™. a_horse_with_no_name has the right answer here.
it is implicitly cast to timestamp with time zone, then It Just Works™. Again, a_horse_with_no_name is right here.
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).
myTimestamp at local
.from_tz
function to explicitly build a value with an explicit time zone other than that of your session.Trying to do any of the above to date will fail as you described:
myTimestamp at time zone 'UTC'
from_tz(myTimestamp, 'America/New_York')
The solution here is to cast the date to a timestamp first:
select from_tz(cast(myTimestamp as timestamp), 'America/New_York') from tableA
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With