How to get the timezone difference as a number

[cmmrfrds]

Right now I am using a hardcoded literal for the time zone difference. The number 21600 as 6 hours. Since I am in Central Time it will be either 5 or 6 hours. I would like to replace the 21600 with a function that returns the offset as a number.

where 1=1
and alog.access_instant >=
(trunc(to_date('01/24/2012','MM/DD/YYYY') - to_date('12/31/1840','MM/DD/YYYY'),0)*86400)+21600
and alog.access_instant < (trunc(to_date('01/25/2012','MM/DD/YYYY') - to_date('12/31/1840','MM/DD/YYYY'),0)*86400)+21600

I can get the time zone abbr but not the number.

select
FROM_TZ(CAST('01-JUL-12' AS TIMESTAMP), 'America/Chicago')
from dual

I appreciate any help. Thank you.

 

[cmmrfrds]

I found a way to get the timezone offset as a number, but it does not resolve to a literal which is what I need for the most efficient partition pruning.

My new Where Clause with a relative number.

where 1=1
and alog.access_instant >=
((trunc(to_date('01/24/2012','MM/DD/YYYY') - to_date('12/31/1840','MM/DD/YYYY'),0)*86400)+
to_number((EXTRACT(TIMEZONE_HOUR FROM FROM_TZ(CAST(to_date('01/24/2012','MM/DD/YYYY') AS TIMESTAMP), 'America/Chicago') )*-1)*3600) )
and alog.access_instant < ((trunc(to_date('01/25/2012','MM/DD/YYYY') - to_date('12/31/1840','MM/DD/YYYY'),0)*86400) +
to_number((EXTRACT(TIMEZONE_HOUR FROM FROM_TZ(CAST(to_date('01/25/2012','MM/DD/YYYY') AS TIMESTAMP), 'America/Chicago') )*-1)*3600))

My first Where Clause resolved to a number. see explain plan.
13 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT PRD.ACCESS_LOG :Q1002 Filter Predicates: "ALOG"."ACCESS_INSTANT">=5398293600 Cost: 7,255 Bytes: 407,987,321 Cardinality: 6,089,363 Partition #: 20 Partitions accessed #209 - #224

My latest Where Clause does not resolve to a number so that the pruning is done by value instead of literal.

13 TABLE ACCESS FULL TABLE PARALLEL_COMBINED_WITH_PARENT PRD.ACCESS_LOG :Q1002 Filter Predicates: "ALOG"."ACCESS_INSTANT">=5398272000+TO_NUMBER(TO_CHAR(EXTRACT(TIMEZONE_HOUR FROM FROM_TZ(CAST(TO_DATE(' 2012-01-24 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AS TIMESTAMP),'America/Chicago'))*(-1)*3600)) AND "ALOG"."ACCESS_INSTANT"Cost: 359,245 Bytes: 119,989,610 Cardinality: 1,845,994 Partition #: 21 Partitions determined by Key Values

Notice is does not resolve directly to the value but instead is a value with a relative offset. I need to come up with a way to have the where clause value resolve to a literal. Any ideas?