Oracle query using time component not returning expected results

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

Aaron Shilo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

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

johnsone

@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.

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

ASKER

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.

ASKER

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

wdosanjos

What's the expected result for the query?

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

wdosanjos,

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

ASKER

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 . . .

SOLUTION

wdosanjos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

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

>>>> 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

ASKER

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.

so the conditions I put in http:#35018667 worked then?

great

glad I could help
please remember to close the question

ASKER

increase points

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