Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to get/extract hour part from timestamp with time zone?

Andy HardyJun 8 2009 — edited Jun 8 2009
Using Oracle 10g, I have a timestamp with time zone.

We have processing that uses hourly intervals (as a offset from the processing day). I want to 'get' the hour contained within a timestamp so that I can compare it to one of our hourly offsets e.g. (pseudo code)

if the hour within the time stamp = 0 then
do midnight processing
else
do other hourly processing
fi

I thought that 'extract' was the correct way to obtain the hour part of the timestamp, but this returns an interval based on UTC - so where I might expect

SELECT extract (hour FROM TIMESTAMP '2009-06-08 00:34:56 Europe/London') FROM dual

to return 0, it's actually returning 23 as that is the UTC hour matching the time zone.

So... how should I be obtaining the hour in order to have it make 'sense' in the context of its time zone? Do I simply use to_char and parse out the hour part?

Thoughts?

--
Andy
This post has been answered by 94799 on Jun 8 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 6 2009
Added on Jun 8 2009
4 comments
2,536 views