Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 645
  • Last Modified:

Oracle query using time component not returning expected results

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
Alaska Cowboy
Asked:
Alaska Cowboy
  • 8
  • 8
  • 2
  • +2
5 Solutions
 
sdstuberCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Aaron ShiloChief Database ArchitectCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
johnsoneSenior Oracle DBACommented:
@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
 
sdstuberCommented:
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
 
Alaska CowboyAuthor Commented:
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
 
Alaska CowboyAuthor Commented:
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
 
wdosanjosCommented:
What's the expected result for the query?
0
 
sdstuberCommented:
>>>> 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
 
Alaska CowboyAuthor Commented:
wdosanjos,

I have two data loads, one before 12:50am and one after 3:30am, so I'm trying to separate them.
0
 
Alaska CowboyAuthor Commented:
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
 
wdosanjosCommented:
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
 
Alaska CowboyAuthor Commented:
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
 
sdstuberCommented:
>>>> 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
 
Alaska CowboyAuthor Commented:
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
 
sdstuberCommented:
so the conditions I put in http:#35018667  worked then?

 great

glad I could help
please remember to close the question
0
 
Alaska CowboyAuthor Commented:
increase points
0
 
sdstuberCommented:
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
 
Alaska CowboyAuthor Commented:
thanks for the multiple responses.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now