{"id":850,"date":"2026-07-17T17:13:22","date_gmt":"2026-07-17T15:13:22","guid":{"rendered":"https:\/\/ilmarkerm.eu\/blog\/?p=850"},"modified":"2026-07-17T17:13:22","modified_gmt":"2026-07-17T15:13:22","slug":"extract-from-timestamp-with-time-zone","status":"publish","type":"post","link":"https:\/\/ilmarkerm.eu\/blog\/2026\/07\/extract-from-timestamp-with-time-zone\/","title":{"rendered":"EXTRACT from TIMESTAMP WITH TIME ZONE"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">Pre-vacation, friday evening, another temporal nugget for you.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">EXTRACT from TIMESTAMP WITH TIME ZONE returns values in UTC. <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACT-datetime.html\" data-type=\"link\" data-id=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/EXTRACT-datetime.html\">This is all documented<\/a>, but I think quite unintuitive. Just have to keep it in mind.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)\n  2  select q.t, extract(day from q.t), extract(hour from q.t)\n  3  from q;\n\nT                                    EXTRACT(DAYFROMQ.T) EXTRACT(HOURFROMQ.T)\n------------------------------------ ------------------- --------------------\n2026-07-17 01.00.00,000000000 +03:00                  16                   22<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Solution is to convert the value to TIMESTAMP before using EXTRACT.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)\n  2  select q.t, extract(day from cast(q.t as timestamp)) d, extract(hour from cast(q.t as timestamp)) h\n  3  from q;\n\nT                                             D          H\n------------------------------------ ---------- ----------\n2026-07-17 01.00.00,000000000 +03:00         17          1<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Mandatory version information: 19.31<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Pre-vacation, friday evening, another temporal nugget for you. EXTRACT from TIMESTAMP WITH TIME ZONE returns values in UTC. This is all documented, but I think quite unintuitive. Just have to keep it in mind. Solution is to convert the value to TIMESTAMP before using EXTRACT. Mandatory version information: 19.31<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[4],"class_list":["post-850","post","type-post","status-publish","format-standard","hentry","category-blog-entry","tag-oracle"],"_links":{"self":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/850","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/comments?post=850"}],"version-history":[{"count":1,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/850\/revisions"}],"predecessor-version":[{"id":851,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/posts\/850\/revisions\/851"}],"wp:attachment":[{"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/media?parent=850"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/categories?post=850"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ilmarkerm.eu\/blog\/wp-json\/wp\/v2\/tags?post=850"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}