INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

How to get the timezone difference as a number

How to get the timezone difference as a number

(OP)
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.

RE: How to get the timezone difference as a number

(OP)
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?

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close