Solved

Oracle query using time component not returning expected results

Posted on 2011-03-02
21
633 Views
Last Modified: 2012-05-11
I'm running this query but not getting expected results

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

The above query returns the following rows, but it's "less than" the time component:

Seqno                      Paid Dt             Load Dt                            Update Date
190000007066923      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066924      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066925      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
190000007066932      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM


Oracle-query-with-time-component.pdf
0
Comment
Question by:Alaska Cowboy
  • 8
  • 8
  • 2
  • +2
21 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 125 total points
ID: 35018624
and trunc(load_dt) = '2-mar-2011'
    and trunc(load_dt) < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

these don't make much sense

the first should have a to_date on it, or  better yet use a range query
also,  every time on March 2, 2011 will be truncated to be less than 3:31:42am.

trunc(noon of a day) = trunc(1am of that day) = trunc(7pm of that day) = 00:00:00 of that day


0
 
LVL 34

Assisted Solution

by:johnsone
johnsone earned 50 total points
ID: 35018627
You are comparing to TRUNC(LOAD_DT).  That would make the time component midnight for comparison purposes.  If you really want less than than your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;
0
 
LVL 15

Assisted Solution

by:Aaron Shilo
Aaron Shilo earned 50 total points
ID: 35018637
try this :

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and to_char(load_dt,'dd-mon-rr') = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;


0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35018667
by a range query I mean this...

      and load_dt >= to_date( '2-mar-2011' ,'dd-mon-yyyy')
      and load_dt < to_date( '2-mar-2011' ,'dd-mon-yyyy') +1

if you only want times that occur before 3:31:42 am then don't trunc it

    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

if you have any indexes on load_dt,  you'll probably find using these 3 conditions instead of the two TRUNC calls will be faster
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35018703
to_char(load_dt,'dd-mon-rr')

won't work  because the  "rr" format will produce 11,  not 2011

also,  it suffers from the same index problem that TRUNC does
you can get around that with function based indexes but range queries are more versatile
with the only cost being a few keystrokes
0
 
LVL 34

Expert Comment

by:johnsone
ID: 35018709
@ashilo

 to_char(load_dt,'dd-mon-rr') = '2-mar-2011'

????

That condition will never be true.  The TO_CHAR would output 02-mar-11, which can never match.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35018736
you could change the rr to rrrr or yyyy,  but it's not an improvement over the trunc.

The data conversion should be on the constant, not the column.  That way the conversion cost is absorbed once at parse time, not once per row at runtime
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018749
sdstuber,

>>if you only want times that occur before 3:31:42 am then don't trunc it
and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018791
johnsone,

>>your query should look like this:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

-- but I don't want records prior to March 2
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35018862
What's the expected result for the query?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 125 total points
ID: 35018903
>>>> sdstuber,

>>>>>>if you only want times that occur before 3:31:42 am then don't trunc it
>>>>and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')

>>>>but that's what I did . . . the only difference is I limited records to those loaded on Mar 2.

Check your original query posted in the question you DID put a trunc on the load_dt,

that's the problem.  because trunc(load_dt) will be 2011-03-02 00:00:00  for ALL date values of March 2.

So,   these are all the same value...
trunc(2011-03-02 00:00:00)
trunc(2011-03-02 03:31:42)
trunc(2011-03-02 15:27:14)
trunc(2011-03-02 19:00:00)
trunc(2011-03-02 23:59:59)

if you only want the load_dt values < 3:31:42,  then DON'T use trunc
if that's not what you want,  please rexplain the requirements,  or better yet

Provide sample data and expected output (preferably in text, not images, so we can import it to test with)
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018909
wdosanjos,

I have two data loads, one before 12:50am and one after 3:30am, so I'm trying to separate them.
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018930
Ok, I eliminated the "trunc(load_dt) = '2-mar-2011'

I ran this

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
     and trunc(load_dt) = to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

and got 0 records returned, but you can see from my data (and the attachment on my original post) that the time component is 3:31:42

so something's not right with my time logic . . .
0
 
LVL 23

Assisted Solution

by:wdosanjos
wdosanjos earned 25 total points
ID: 35018975
This should give you the load before 12:50 am:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 190000000000000 and 190999999999999
    and trunc(load_dt) = '2-mar-2011'
    and load_dt < to_date('02-Mar-2011 3:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Open in new window

0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35018989
This also returns 0 rows

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) > to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

but the time component is clearly

This returns rows, but it sure doesn't look right:

select pt.clm_line_tier_seqno, pt.clm_paid_dt, pt.load_dt, pt.activity_timestamp
   from ods.pclm_clm_line_tier pt
  where clm_line_tier_seqno between 140000000000000 and 140999999999999
    and trunc(load_dt) < to_date('02-Mar-2011 01:31:42 AM','dd-mon-yyyy hh:mi:ss PM')
    order by load_dt desc;

Seq No                     Paid Dt                 Load Dt                             Update Date
140000007066963      01-Mar-2011      02-Mar-2011 3:31:42 AM      02-Mar-2011 3:31:42 AM
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35018992
>>>> so something's not right with my time logic . . .

yes, exactly my point above
 DON'T USE TRUNC  - you're making ALL times equivalent by doing that.

TRUNC(sysdate)  = trunc(sysdate + 1/1000)


if my query didn't return the results you wanted,  
please explain what it did wrong or what it did not do right
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019127
oh, oops, I'm terribly sorry.

I was blind to the trunc( ) on my time component, when you all explained it I was thinking of the first trunc and didn't even see it on the time component because.

So, I'm good but thank you all for the assistance.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35019164
so the conditions I put in http:#35018667  worked then?

 great

glad I could help
please remember to close the question
0
 
LVL 1

Author Comment

by:Alaska Cowboy
ID: 35019652
increase points
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 35019765
as the asker you have to increase the points if you want to.

or request attention and a Moderator will do it for you.

I can too as a Zone Advisor, but don't do so unless specifically instructed to since it's a conflict of interest as I could be rewarding myself
0
 
LVL 1

Author Closing Comment

by:Alaska Cowboy
ID: 35019819
thanks for the multiple responses.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now