CODEWITHSUNDEEP
sqloracle11gtimestamp
Zombraz
Zombraz

Reputation: 373

Extract(hour from ...) wrong hour extracted

I'm trying to make a report depending on the number of rows created on the same hour, and I've tried to group rows using the extract function, but it changes the information on the result, for example:

 select 
 to_char(resolved_time,'YY/MM/DD') Day,
 to_char(resolved_time, 'HH24') hour_tmp,
 extract(hour from resolved_time) Hour
 from RESOLUTION_HISTORY h
 where resolved_time >= timestamp '2017-01-01 00:00:00'
 and resolved_time < timestamp '2017-01-02 00:00:00'
 and modified_by in 'user1'
 order by 2;

and my result looks something like this:

Day         Hour_Tmp    Hour
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    14          20
17/01/01    15          21
17/01/01    17          23
17/01/01    17          23
17/01/01    18          0

As you can see, theres a difference between the actual value of resolved time and the extract, tried to alter my session but it doesnt seems to help. Is there any other option to change this setting on a local session?

Upvotes: 0

Views: 826

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

The difference you are seeing is the offset between UTC and the time zone associated with your column values. You can see a simplified version with:

select extract(hour from timestamp '2017-01-01 00:00:00 America/New_York') from dual;

EXTRACT(HOURFROMTIMESTAMP'2017-01-0100:00:00AMERICA/NEW_YORK')
--------------------------------------------------------------
                                                             5

DBTIMEZONE is +00:00 as recommended, the database server is on BST, and my session is also BST (via Europe/London). But none of that is relevant.

Buried in the documentation:

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

(If your column was timestamp with local time zone you'd see the hour in your session time zone, which is possibly even less useful.)

You can work around it by casting your column to a plain timestamp, which essentially truncates the time zone info:

extract(hour from cast(resolved_time as timestamp))

and with your data you can see the difference with:

select 
 to_char(resolved_time,'YYYY-MM-DD HH24:MI:SS TZR') full_value,
 to_char(resolved_time,'YY/MM/DD') Day,
 to_char(resolved_time, 'HH24') hour_tmp,
 extract(hour from resolved_time) Hour_utc,
 extract(hour from cast(resolved_time as timestamp)) Hour
 from RESOLUTION_HISTORY h
 where resolved_time >= timestamp '2017-01-01 00:00:00 America/New_York'
 and resolved_time < timestamp '2017-01-02 00:00:00 America/New_York'
 and modified_by in 'user1'
 order by 2;

FULL_VALUE                                           DAY      HO   HOUR_UTC       HOUR
---------------------------------------------------- -------- -- ---------- ----------
2017-01-01 12:00:00 AMERICA/NEW_YORK                 17/01/01 12         17         12

Incidentally, I've included the (assumed) column time zone value in the filter condition so it doesn't have to rely on your session timezone. If you have values from multiple time zones then you need to allow for that in your filter too, presumably, and might need to convert your column values to a specific zone before extracting and grouping. Depends what you're trying to do though.

Also see Tony’s Tirade against TIMESTAMP WITH TIME ZONE.

Upvotes: 1

Related Questions