How to get/extract hour part from timestamp with time zone?
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