Written by: ilmarkerm

Category: Blog entry

Published: July 17, 2026

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.

SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)
  2  select q.t, extract(day from q.t), extract(hour from q.t)
  3  from q;

T                                    EXTRACT(DAYFROMQ.T) EXTRACT(HOURFROMQ.T)
------------------------------------ ------------------- --------------------
2026-07-17 01.00.00,000000000 +03:00                  16                   22

Solution is to convert the value to TIMESTAMP before using EXTRACT.

SQL> with q as (select timestamp'2026-07-17 01:00:00+03:00' t from dual)
  2  select q.t, extract(day from cast(q.t as timestamp)) d, extract(hour from cast(q.t as timestamp)) h
  3  from q;

T                                             D          H
------------------------------------ ---------- ----------
2026-07-17 01.00.00,000000000 +03:00         17          1

Mandatory version information: 19.31