2

Will using the following query to retrieve the UTC timestamp from an oracle database cause an issue? I do not want to alter the database timezone parameter in order to retrieve the correct date. I do not want to do alter database set time_zone.

My query at the moment is:

select from_tz(CAST (sys_extract_utc(systimestamp) AS TIMESTAMP), '+00:00') from dual;

I would like to know if this query will result in the correct UTC date in all circumstances regardless of the EST/EDT status.

Hooplator15
  • 1,540
  • 7
  • 31
  • 58
  • You can simply do `select sys_extract_utc(systimestamp) from dual;` - that's all. Using `from_tz(... sys_extract_utc() '+00:00')` is redundant. – Wernfried Domscheit Nov 01 '16 at 17:51
  • Yes, but that does not include the time zone append. I am looking for consistent data. Is the return type of that query still a `TIMESTAMP WITH TIME ZONE`? – Hooplator15 Nov 01 '16 at 17:54
  • 1
    `EXTRACT_UTC` returns a `TIMESTAMP` value. If you like to get `TIMESTAMP WITH TIME ZONE` then `select systimestamp at time zone 'UTC' from dual;` should do it. – Wernfried Domscheit Nov 01 '16 at 18:03
  • Database timezone (i.e. `DBTIMEZONE`) has **no** effect on SYSTIMESTAMP or any other time function. – Wernfried Domscheit Nov 01 '16 at 18:12
  • Ok, I figured it might not be an issue but I am going with your `select systimestamp at time zone 'UTC' from dual` for my final solution for consistency. Thank you. – Hooplator15 Nov 01 '16 at 18:20

3 Answers3

2

I don't see anything wrong with your query. Note that if you want to work in UTC for your session, you could simply:

ALTER SESSION SET TIME_ZONE = '0:00';
select CURRENT_TIMESTAMP from dual;

output

11/1/2016 5:48:55.115282 PM +00:00

That would change your current_timestamp (and localtimestamp) for your entire session.

tbone
  • 15,107
  • 3
  • 33
  • 40
1

Your query is just setting timezone but not converting time. If that's what you were looking for it is ok. Is local time is 3AM you will return 3AM UTC. I think you're looking for that query:

select cast(sysdate as timestamp) at time zone 'UTC' from dual;

And the apply 

select from_tz(cast(systimestamp at time zone 'UTC' as timestamp), '+0:00') from dual;
Kacper
  • 4,798
  • 2
  • 19
  • 34
  • Thank you. One thing I noticed with your second query is that it seems to trim trailing time data on my machine. Ex: `select from_tz(CAST (sys_extract_utc(systimestamp) AS TIMESTAMP), '+00:00') as utc1, from_tz(cast(sysdate as timestamp), 'utc') as utc2 from dual;` results in: `11/1/2016 5:36:36.503812 PM +00:00` (for UTC1) and `11/1/2016 1:36:36.000000 PM +00:00` (for UTC2) – Hooplator15 Nov 01 '16 at 17:38
  • The difference is that the seconds portion `36.503812` goes to `36.000000`. – Hooplator15 Nov 01 '16 at 17:38
  • @JohnAugust isn't it due to cast? My answer has `sysdate` if you have `systimestamp` you can ommit `cast` – Kacper Nov 01 '16 at 17:41
  • @JohnAugust select from_tz(cast(systimestamp at time zone 'UTC' as timestamp), '+1:00') from dual; this not cuts seconds on my machine – Kacper Nov 01 '16 at 17:43
  • Oh.. okay you are correct, I did not notice that. Changing to systimestamp works. `select from_tz(CAST (sys_extract_utc(systimestamp) AS TIMESTAMP), '+00:00') as utc1, from_tz(cast(systimestamp as timestamp), 'utc') as utc2 from dual;` – Hooplator15 Nov 01 '16 at 17:44
  • @JohnAugust I've edited to include proper query in my answer – Kacper Nov 01 '16 at 17:45
  • The second query has the same redundancy as in the question. You convert a UTC (`systimestamp at time zone 'UTC'`) time again to UTC. Note, `UTC` is the same as `+0:00` – Wernfried Domscheit Nov 01 '16 at 18:10
  • @WernfriedDomscheit It is not redundancy: `select from_tz(cast(systimestamp at time zone 'UTC' as timestamp), '+0:00') from dual;` 6PM now and `select from_tz(cast(systimestamp as timestamp), '+0:00') from dual;` 7PM so this are different results. My DB is CET – Kacper Nov 01 '16 at 18:13
0

Some general notes:

SYSTIMESTAMP and SYSDATE are given in time zone of your database server's operating system. Thus changing database time zone (i.e. DBTIMEZONE) does not change anything.

CAST (sys_extract_utc(systimestamp) AS TIMESTAMP), resp. cast(systimestamp at time zone 'UTC' as timestamp) have a problem. You convert your timestamp to UTC but by CAST(... AS TIMESTAMP) you remove any time zone information from that value. If you like to do any further conversion (e.g. again to TIMESTAMP WITH TIME ZONE value) then your input UTC value is considered to be a SESSIONTIMEZONE value.

from_tz(CAST (sys_extract_utc(systimestamp) AS TIMESTAMP), '+00:00') does following:

  1. Get current time in time zone of your database server's operating system, include time zone information.
  2. Convert this time to UTC, cut time zone information
  3. Append time zone information (+00:00) to this value

Correct output but redundant conversion

cast(sysdate as timestamp) at time zone 'UTC' does following:

  1. Get current time in time zone of your database server's operating system, without any time zone information.
  2. Cast to TIMESTAMP (basically no effect at all)
  3. Consider this value as time in your local session time zone (SESSIONTIMEZONE) and convert to UTC.

Correct output only if time zone of your database server's operating system is the same as your local session time zone, otherwise you get wrong result.

from_tz(cast(systimestamp at time zone 'UTC' as timestamp), '+0:00') does following:

  1. Get current time in time zone of your database server's operating system, include time zone information.
  2. Convert this time to UTC
  3. Cut time zone information
  4. Append time zone information (+00:00) to this value

Correct output but redundant conversion

from_tz(cast(systimestamp as timestamp), '+0:00') does following:

  1. Get current time in time zone of your database server's operating system, include time zone information.
  2. Cut time zone information
  3. Append time zone information (+00:00) to this value

Correct output only if time zone of your database server's operating system is UTC.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110