find difference in dates in oracle sql

i have a table in oracle 11

I am running this sql to find if a task was created 4 or more days earlier than today's date..  

select * from mytasks a where TRUNC(sysdate) - TRUNC(a.task_created_date) >= 4

Is that sql ok to use to find tasks created 4 days earlier than today's date... Or do I have to set any oracle-environment-variables to make sure it will work in that way...

Will the subtraction -  "TRUNC(sysdate) - TRUNC(a.task_created_date) " will always result in "Days"
ts84zsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Steve WalesSenior Database AdministratorCommented:
Well, you have to understand how dates in Oracle work.

At the simplest level all an Oracle date is, is an integer.  One day is represented as 1.  (this is simplifying how it works but it is what it is :) )

If I subtract today from two days from now , I'll get 2.

An hour is represented by 1/24.
A minute is represented by 1/1440 (Which is 1 (day) divided by 24 (hours) divided by 60 (minutes)).

So, yes, the code you show above is correct for your requirement as I understand it.

It's not that the result is "days" - it's that the result indicates an integer number of days between the two dates.

You can read all about Oracle Dates and Date Arithmetic etc here: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#i1847

(It's Oracle 11gR1 docs, but it's late and I'm tired but the concepts still work).
0
PortletPaulfreelancerCommented:
select * from mytasks a where TRUNC(sysdate) - TRUNC(a.task_created_date) >= 4

That query WILL find tasks created 4 days earlier than today's date

BUT, a more efficient way to achieve the same outcome would be:

select * from mytasks a
where a.task_created_date >= TRUNC(sysdate) - 4

In the first query every row of data is altered by a function, but in the second query the rows of data are not changed at all. The second query uses a "sargable predicate" but the first query does not.

sargable: Search ARGument ABLE

Rules of thumb
Avoid applying functions on data values in a sql condition.
Avoid non-sargable predicates and replace them with sargable equivalents.
0
johnsoneSenior Oracle DBACommented:
First thing.  Oracle dates are not stored as integers.  They are stored as a 7 byte structure.

Not using a function on the table column is a good idea, however the difference in performance is negligible if there is no index on that column.  Also, the query should be:

select * from mytasks a where a.task_created_date <= TRUNC(SYSDATE) - 4

Sign was incorrect.  I also don't think that will give the desired result.  The original is truncating the date in the table, so I believe that this would give the correct result:

select * from mytasks a where a.task_created_date < TRUNC(SYSDATE) - 3

As that would take into account anything that had a time that is other than midnight 4 days ago.   Using today (11/20/2015) as the example, subtracting 4 days would be 11/16/2015 00:00:00, but if the column in the table contained 11/16/2015 07:03:55 that row would not be selected using TRUNC(SYSDATE)-4 but it would be selected in the original query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
>>" created 4 or more days earlier than today's date..  "

yes, when "flipping" the predicate less than should have been used, sorry

adjusting to - 3  also appears to have corrected another error of mine.

not my finest hour it seems.
0
Steve WalesSenior Database AdministratorCommented:
As a note for clarification, I know that dates aren't stored as integers - I did state that that it was simplifying the structure - but from understanding how it works a span of 24 hours is represented by the number 1 - which is simply put, an integer.

You're not going store Jan 1, 4712 BCE through Dec 12, 9999 as a basic integer.

Sorry if that caused any confusion to the OP
0
johnsoneSenior Oracle DBACommented:
The result of a subtraction between 2 dates results in a number of days being returned.  It is not an integer, it is a number (with decimal places) that gives the difference between the 2 dates represented by the number of days.

Not knowing the actual datatype involved in this scenario, if we are talking about a timestamp, rather than a date, the result of that difference is an interval not a number.  If the columns are timestamps, then there is a lot of type conversion happening in the query that is being masked and some of the solutions may not work.

PS.  We are all the victim of missing an AND/OR or sign flipping all the time.
0
Mark GeerlingsDatabase AdministratorCommented:
Basically, try to avoid apply SQL operators (including: trunc, to_char, to_date, upper, lower, etc.) on any columns that you reference in "where" clauses of queries.  Why?  These can add a huge performance penalty there.  It is OK to use these in the "select..." part of the query and on bind variables in the "where" clauses, because in either of those places the performance impact is minimal.

And yes, when you subtract one Oracle "date" value (or timestamp) from another, the results will be expressed as a number representing the number of days (or the portion of a day or days) between those two "date" values.
0
johnsoneSenior Oracle DBACommented:
When subtracting timestamps you do not get a number that is the number of days between the 2 timestamps.  You get an interval returned.
0
ts84zsAuthor Commented:
Thanks so much for all the tips
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.