CODEWITHSUNDEEP
oracle-databaseoracle12ctimestamp-with-timezone
Ronnis
Ronnis

Reputation: 12833

Extract local hour from timestamp with time zone

I'm trying to extract the local hour from a timestamp with time zone, but as I am new to working with this data type, I get unexpected results.

I was expecting 22 as output for every row below.

with my_data as(
   select to_timestamp_tz(ts, 'yyyy-mm-dd hh24:mi:ss tzh:tzm') as tsz
     from (select '2017-12-07 22:23:24 +' || lpad(level, 2, '0') || ':00' as ts  
             from dual connect by level <= 10
          )       
)
select dbtimezone
      ,sessiontimezone
      ,tsz
      ,extract(hour from tsz)
  from my_data;

enter image description here

Why does this happen, and what do I need to do to extract the local hour from a timestamp with time zone?

Upvotes: 3

Views: 4455

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Check documentation of EXTRACT(datetime):

When extracting from a datetime with a time zone value, the value returned is in UTC.

Use TO_CHAR(tsz, 'HH24') or EXTRACT(hour from cast(tsz as timestamp)) if you like to get local hours.

Upvotes: 6

Related Questions