Oracle sql timezone issue

Question

My requirement here is to get time in GMT/UTC from a date type column. But when I use cast to cast date to timestamp, it is using US/Pacific timezone as reference though session timezone is set to GMT. So unless I use from_tz, I am not seeing desired result. Is there any other timezone setting in oracle sql that I need to modify to take GMT as reference always?

alter session set time_zone='+00:00';
select sessiontimezone from dual;
select current_timestamp from dual;
select sys_extract_utc(cast (sysdate as timestamp)) from dual;
select sys_extract_utc(from_tz(cast (sysdate as timestamp), '-07:00')) from dual;
select sys_extract_utc(current_timestamp) from dual;


Session altered.


SESSIONTIMEZONE
---------------------------------------------------------------------------
+00:00


CURRENT_TIMESTAMP
---------------------------------------------------------------------------
11-APR-16 08.46.42.292173 AM +00:00


SYS_EXTRACT_UTC(CAST(SYSDATEASTIMESTAMP))
---------------------------------------------------------------------------
11-APR-16 01.46.42.000000 AM

SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATEASTIMESTAMP),'-07:00'))
---------------------------------------------------------------------------
11-APR-16 08.46.42.000000 AM


SYS_EXTRACT_UTC(CURRENT_TIMESTAMP)
---------------------------------------------------------------------------
11-APR-16 08.46.42.295310 AM

Tasks table has a date type column called task_started. I am looking to get UTC time from this date field. As part of that I was trying to alter session timezone to GMT while inserting the data so that I can simply cast it back to timestamp which is not working.

select task_started from tasks where rownum <2;

TASK_STAR
---------
10-APR-16

desc tasks;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
...
 TASK_STARTED                                       DATE
...

Answer 1

This is a demo using data inserted using sysdate on a system on London time, so currently on BST (+01:00). The difference is smaller than you'd see on the west coast but the same things apply.

Mimicking your table, you can see that the session time zone has no effect on the inserted value since sysdate uses the database server time, not the session (client) time (as explained here):

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF3';
alter session set nls_timestamp_tz_format = 'YYYY-MM-DD HH24:MI:SS.FF3 TZH:TZM';

create table tasks (id number, task_started date);

alter session set time_zone='America/Los_Angeles';

insert into tasks (id, task_started) values (1, sysdate);

select task_started, cast(task_started as timestamp) as ts
from tasks where rownum < 2;

TASK_STARTED        TS                    
------------------- -----------------------
2016-04-11 11:55:59 2016-04-11 11:55:59.000

alter session set time_zone = 'UTC';

select task_started, cast(task_started as timestamp) as ts
from tasks where rownum < 2;

TASK_STARTED        TS                    
------------------- -----------------------
2016-04-11 11:55:59 2016-04-11 11:55:59.000

So that's the BST time in both cases. There isn't any session or database setting that will show you a date (or timestamp, without a time zone) converted to a specific time zone automatically. The database doesn't know what that stored date/time represents unless you tell it. It doesn't know or case if you use sysdate, current_date, or a date literal; at the point the data is inserted into the table it has no time zone information at all.

To get the UTC equivalent you need to use from_tz to declare that the stored value represents a specific time zone; then you can use at time zone (which keeps the time zone info) or sys_extract_utc (which doesn't) on that to convert it; and optionally cast back to a date:

select from_tz(cast(task_started as timestamp), 'Europe/London') as db_tstz,
  from_tz(cast(task_started as timestamp), 'Europe/London') at time zone 'UTC' as utc_tstz,
  sys_extract_utc(from_tz(cast(task_started as timestamp), 'Europe/London')) as utc_ts,
  cast(sys_extract_utc(from_tz(cast(task_started as timestamp), 'Europe/London')) as date) as utc_date
from tasks where rownum < 2;

DB_TSTZ                        UTC_TSTZ                       UTC_TS                  UTC_DATE          
------------------------------ ------------------------------ ----------------------- -------------------
2016-04-11 11:55:59.000 +01:00 2016-04-11 10:55:59.000 +00:00 2016-04-11 10:55:59.000 2016-04-11 10:55:59

I've used the time zone region name so it takes care of summer time for me; you could use dbtimezone but that would always use -08:00, and as you showed in the question you need to use -07:00 at the moment. ANd you could use sessiontimezone but then you have to remember to set that properly. Obviously in your case you'd use your local region, e.g. America/Los_Angeles, instead of Europe/London.

And from that you can get the epoch, via an interval by comparing timestamps or more simply from comparing dates:

select sys_extract_utc(from_tz(cast(task_started as timestamp), 'Europe/London'))
    - timestamp '1970-01-01 00:00:00' as diff_interval,
  cast(sys_extract_utc(from_tz(cast(task_started as timestamp), 'Europe/London')) as date)
    - date '1970-01-01' as diff_days,
  86400 *
    (cast(sys_extract_utc(from_tz(cast(task_started as timestamp), 'Europe/London')) as date)
      - date '1970-01-01') as epoch
from tasks where rownum < 2;

DIFF_INTERVAL    DIFF_DAYS       EPOCH
---------------- --------- -----------
16902 10:55:59.0  16902.46  1460372159

If you put 1460372159 into an online converter (there are many) it will show the UTC time.