Converting a DATE to DATE in UTC time : sys_extract_utc behaving differently for sysdate and systimestamp
select sysdate from dual;
15-NOV-2018 23:09:31
select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM
select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM
But,
select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00
select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM
Further,
select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC
Why is sys_extract_utc(cast(sysdate as TIMESTAMP))
different than sys_extract_utc(systimestamp)
in my db?
The time given by sys_extract_utc(systimestamp)
is the correct UTC time, btw.
My core requirement is converting data from a table with a DATE column to "DATE in UTC time zone" in a select query. It is of course not working (with either sys_extract_utc
or with at time zone 'UTC'
), which is probably because of the same reason the above conversion with sysdate is not working.
In case it helps, dbtimezone is:
SELECT DBTIMEZONE FROM DUAL;
+00:00
sql oracle
|
show 1 more comment
select sysdate from dual;
15-NOV-2018 23:09:31
select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM
select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM
But,
select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00
select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM
Further,
select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC
Why is sys_extract_utc(cast(sysdate as TIMESTAMP))
different than sys_extract_utc(systimestamp)
in my db?
The time given by sys_extract_utc(systimestamp)
is the correct UTC time, btw.
My core requirement is converting data from a table with a DATE column to "DATE in UTC time zone" in a select query. It is of course not working (with either sys_extract_utc
or with at time zone 'UTC'
), which is probably because of the same reason the above conversion with sysdate is not working.
In case it helps, dbtimezone is:
SELECT DBTIMEZONE FROM DUAL;
+00:00
sql oracle
1
return type ofsystimestamp
isTIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a propertimestamp with time zone
that has a time zone offset (-08:00
) or region name.DATE
type cast to aTIMESTAMP
doesn't have that information.
– Kaushik Nayak
Nov 16 '18 at 7:47
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
1
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
1
Pardon me .. second argtz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.
– Kaushik Nayak
Nov 16 '18 at 9:10
|
show 1 more comment
select sysdate from dual;
15-NOV-2018 23:09:31
select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM
select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM
But,
select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00
select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM
Further,
select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC
Why is sys_extract_utc(cast(sysdate as TIMESTAMP))
different than sys_extract_utc(systimestamp)
in my db?
The time given by sys_extract_utc(systimestamp)
is the correct UTC time, btw.
My core requirement is converting data from a table with a DATE column to "DATE in UTC time zone" in a select query. It is of course not working (with either sys_extract_utc
or with at time zone 'UTC'
), which is probably because of the same reason the above conversion with sysdate is not working.
In case it helps, dbtimezone is:
SELECT DBTIMEZONE FROM DUAL;
+00:00
sql oracle
select sysdate from dual;
15-NOV-2018 23:09:31
select cast(sysdate as TIMESTAMP) from dual;
15-NOV-18 11.07.47.000000000 PM
select sys_extract_utc(cast(sysdate as TIMESTAMP)) from dual;
15-NOV-18 05.40.35.000000000 PM
But,
select systimestamp from dual;
15-NOV-18 11.11.16.345959000 PM -08:00
select sys_extract_utc(systimestamp) from dual;
16-NOV-18 07.08.49.772214000 AM
Further,
select cast(sysdate as TIMESTAMP) at time zone 'UTC' from dual;
15-NOV-18 05.48.11.000000000 PM UTC
Why is sys_extract_utc(cast(sysdate as TIMESTAMP))
different than sys_extract_utc(systimestamp)
in my db?
The time given by sys_extract_utc(systimestamp)
is the correct UTC time, btw.
My core requirement is converting data from a table with a DATE column to "DATE in UTC time zone" in a select query. It is of course not working (with either sys_extract_utc
or with at time zone 'UTC'
), which is probably because of the same reason the above conversion with sysdate is not working.
In case it helps, dbtimezone is:
SELECT DBTIMEZONE FROM DUAL;
+00:00
sql oracle
sql oracle
edited Nov 16 '18 at 8:47
Arnab Bhagabati
asked Nov 16 '18 at 7:21
Arnab BhagabatiArnab Bhagabati
1,80811945
1,80811945
1
return type ofsystimestamp
isTIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a propertimestamp with time zone
that has a time zone offset (-08:00
) or region name.DATE
type cast to aTIMESTAMP
doesn't have that information.
– Kaushik Nayak
Nov 16 '18 at 7:47
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
1
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
1
Pardon me .. second argtz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.
– Kaushik Nayak
Nov 16 '18 at 9:10
|
show 1 more comment
1
return type ofsystimestamp
isTIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a propertimestamp with time zone
that has a time zone offset (-08:00
) or region name.DATE
type cast to aTIMESTAMP
doesn't have that information.
– Kaushik Nayak
Nov 16 '18 at 7:47
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
1
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
1
Pardon me .. second argtz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.
– Kaushik Nayak
Nov 16 '18 at 9:10
1
1
return type of
systimestamp
is TIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a proper timestamp with time zone
that has a time zone offset (-08:00
) or region name. DATE
type cast to a TIMESTAMP
doesn't have that information.– Kaushik Nayak
Nov 16 '18 at 7:47
return type of
systimestamp
is TIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a proper timestamp with time zone
that has a time zone offset (-08:00
) or region name. DATE
type cast to a TIMESTAMP
doesn't have that information.– Kaushik Nayak
Nov 16 '18 at 7:47
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
1
1
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
1
1
Pardon me .. second arg
tz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.– Kaushik Nayak
Nov 16 '18 at 9:10
Pardon me .. second arg
tz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.– Kaushik Nayak
Nov 16 '18 at 9:10
|
show 1 more comment
1 Answer
1
active
oldest
votes
Function SYS_EXTRACT_UTC
works only with TIMESTAMP WITH TIME ZONE
or TIMESTAMP WITH LOCAL TIME ZONE
values. DATE
and TIMESTAMP
do not contain any time zone information, so SYS_EXTRACT_UTC
is basically useless on DATE
or TIMESTAMP
values.
When you run
SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))
then Oracle performs an implicit cast and actually runs
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))
SYSDATE
and SYSTIMESTAMP
returns time in time zone of database server's operating system (NOT DBTIMEZONE
) whereas your command uses the current session SESSIONTIMEZONE
If you insist to use a DATE
value then you could use this one:
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))
TO_CHAR(SYSTIMESTAMP, 'tzr')
provides you the time zone of database server's operating system.
Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich
but altering twice a year between +01:00
and +02:00
due to daylight saving times. For SYSDATE
and SYSTIMESTAMP
this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00
then you will get wrong result if you run query from above today in November.
If your database server utilize daylight saving times then better use a query like
SYS_EXTRACT_UTC(FROM_TZ(CAST(arbitrary DATE value AS TIMESTAMP), 'Europe/Zurich'))
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333171%2fconverting-a-date-to-date-in-utc-time-sys-extract-utc-behaving-differently-for%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Function SYS_EXTRACT_UTC
works only with TIMESTAMP WITH TIME ZONE
or TIMESTAMP WITH LOCAL TIME ZONE
values. DATE
and TIMESTAMP
do not contain any time zone information, so SYS_EXTRACT_UTC
is basically useless on DATE
or TIMESTAMP
values.
When you run
SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))
then Oracle performs an implicit cast and actually runs
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))
SYSDATE
and SYSTIMESTAMP
returns time in time zone of database server's operating system (NOT DBTIMEZONE
) whereas your command uses the current session SESSIONTIMEZONE
If you insist to use a DATE
value then you could use this one:
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))
TO_CHAR(SYSTIMESTAMP, 'tzr')
provides you the time zone of database server's operating system.
Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich
but altering twice a year between +01:00
and +02:00
due to daylight saving times. For SYSDATE
and SYSTIMESTAMP
this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00
then you will get wrong result if you run query from above today in November.
If your database server utilize daylight saving times then better use a query like
SYS_EXTRACT_UTC(FROM_TZ(CAST(arbitrary DATE value AS TIMESTAMP), 'Europe/Zurich'))
add a comment |
Function SYS_EXTRACT_UTC
works only with TIMESTAMP WITH TIME ZONE
or TIMESTAMP WITH LOCAL TIME ZONE
values. DATE
and TIMESTAMP
do not contain any time zone information, so SYS_EXTRACT_UTC
is basically useless on DATE
or TIMESTAMP
values.
When you run
SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))
then Oracle performs an implicit cast and actually runs
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))
SYSDATE
and SYSTIMESTAMP
returns time in time zone of database server's operating system (NOT DBTIMEZONE
) whereas your command uses the current session SESSIONTIMEZONE
If you insist to use a DATE
value then you could use this one:
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))
TO_CHAR(SYSTIMESTAMP, 'tzr')
provides you the time zone of database server's operating system.
Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich
but altering twice a year between +01:00
and +02:00
due to daylight saving times. For SYSDATE
and SYSTIMESTAMP
this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00
then you will get wrong result if you run query from above today in November.
If your database server utilize daylight saving times then better use a query like
SYS_EXTRACT_UTC(FROM_TZ(CAST(arbitrary DATE value AS TIMESTAMP), 'Europe/Zurich'))
add a comment |
Function SYS_EXTRACT_UTC
works only with TIMESTAMP WITH TIME ZONE
or TIMESTAMP WITH LOCAL TIME ZONE
values. DATE
and TIMESTAMP
do not contain any time zone information, so SYS_EXTRACT_UTC
is basically useless on DATE
or TIMESTAMP
values.
When you run
SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))
then Oracle performs an implicit cast and actually runs
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))
SYSDATE
and SYSTIMESTAMP
returns time in time zone of database server's operating system (NOT DBTIMEZONE
) whereas your command uses the current session SESSIONTIMEZONE
If you insist to use a DATE
value then you could use this one:
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))
TO_CHAR(SYSTIMESTAMP, 'tzr')
provides you the time zone of database server's operating system.
Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich
but altering twice a year between +01:00
and +02:00
due to daylight saving times. For SYSDATE
and SYSTIMESTAMP
this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00
then you will get wrong result if you run query from above today in November.
If your database server utilize daylight saving times then better use a query like
SYS_EXTRACT_UTC(FROM_TZ(CAST(arbitrary DATE value AS TIMESTAMP), 'Europe/Zurich'))
Function SYS_EXTRACT_UTC
works only with TIMESTAMP WITH TIME ZONE
or TIMESTAMP WITH LOCAL TIME ZONE
values. DATE
and TIMESTAMP
do not contain any time zone information, so SYS_EXTRACT_UTC
is basically useless on DATE
or TIMESTAMP
values.
When you run
SYS_EXTRACT_UTC(CAST(SYSDATE AS TIMESTAMP))
then Oracle performs an implicit cast and actually runs
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), SESSIONTIMEZONE))
SYSDATE
and SYSTIMESTAMP
returns time in time zone of database server's operating system (NOT DBTIMEZONE
) whereas your command uses the current session SESSIONTIMEZONE
If you insist to use a DATE
value then you could use this one:
SYS_EXTRACT_UTC(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), TO_CHAR(SYSTIMESTAMP, 'tzr')))
TO_CHAR(SYSTIMESTAMP, 'tzr')
provides you the time zone of database server's operating system.
Just a note, very often the time zone of database server's operating system is not a region like Europe/Zurich
but altering twice a year between +01:00
and +02:00
due to daylight saving times. For SYSDATE
and SYSTIMESTAMP
this does not matter because the current UTC offset is always correct. But for example, if my table contains a DATE value 2018-08-01 12:00:00
then you will get wrong result if you run query from above today in November.
If your database server utilize daylight saving times then better use a query like
SYS_EXTRACT_UTC(FROM_TZ(CAST(arbitrary DATE value AS TIMESTAMP), 'Europe/Zurich'))
edited Nov 16 '18 at 12:07
answered Nov 16 '18 at 8:50
Wernfried DomscheitWernfried Domscheit
24.9k43261
24.9k43261
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53333171%2fconverting-a-date-to-date-in-utc-time-sys-extract-utc-behaving-differently-for%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
return type of
systimestamp
isTIMESTAMP WITH TIME ZONE
SYS_EXTRACT_UTC
can only extract from a propertimestamp with time zone
that has a time zone offset (-08:00
) or region name.DATE
type cast to aTIMESTAMP
doesn't have that information.– Kaushik Nayak
Nov 16 '18 at 7:47
Any idea how to convert the date in my select query to <date in UTC> ?
– Arnab Bhagabati
Nov 16 '18 at 8:05
I need convert a column of DATE datatype of a table, not sysdate/systimestamp
– Arnab Bhagabati
Nov 16 '18 at 8:13
1
How do you know what time zone the date values in your table are nominally in? You convert to UTC from some other tome zone - so do you know you can safely assume which zone you're coming from?
– Alex Poole
Nov 16 '18 at 8:36
1
Pardon me .. second arg
tz_offset(dbtimezone)
part in my comment is not correct . Refer Wernfried's answer.– Kaushik Nayak
Nov 16 '18 at 9:10