This discussion is archived
14 Replies Latest reply: Jan 14, 2013 4:50 PM by onedbguru RSS

TRUNC(SYSDATE) question

DBA112 Newbie
Currently Being Moderated
Dear SQL Experts,

Can you clarify the difference b/w the below 2 conditions. I see same results with either of the conditions. I'm trying to pull data for 24 hours and this is one of the conditions I'm using in the SQL:
trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate-1)

trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)

Edited by: DBA112 on Jan 14, 2013 1:57 PM
  • 1. Re: TRUNC(SYSDATE) question
    IckyIckyChiMoon Explorer
    Currently Being Moderated
    Try this (better sql):

    where ....
    and BEGIN_INTERVAL_TIME >= trunc(sysdate-1) /* yesterday at midnight */
    and BEGIN_INTERVAL_TIME < trunc(sysdate) /* today at midnight */


    You won't see any difference because you are truncating both sides of the inequality.
  • 2. Re: TRUNC(SYSDATE) question
    Justin Cave Oracle ACE
    Currently Being Moderated
    trunc(sysdate) returns today at midnight
    trunc(sysdate-1) returns yesterday at midnight
    trunc(begin_interval_time), assuming BEGIN_INTERVAL_TIME is a DATE, returns midnight on whatever day the particular DATE value is on.

    So
    trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate-1)
    returns all rows where the BEGIN_INTERVAL_TIME was some time yesterday. Of course, it would be more logical to either do
    trunc(begin_interval_time) = trunc(sysdate-1)
    or
    BEGIN_INTERVAL_TIME >= trunc(sysdate-1) 
    and begin_interval_time < trunc(sysdate)
    depending on whether there is an index on BEGIN_INTERVAL_TIME that could be used.

    In contrast
    trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)
    returns everything from the 48 hour period between midnight yesterday and 11:59:59pm today.

    If you aren't seeing a difference in the results, that implies that there is no data in the table where the BEGIN_INTERVAL_TIME is from some time today. If, as I'm guessing, you have a job that copies the data from the AWR tables to some custom tables every night, I would guess that this job hasn't run yet for today's data.

    Justin

    Edited by: Justin Cave on Jan 14, 2013 5:05 PM

    Sigh... As rp0428 points out below, I had a small brain cramp and typed "tomorrow" rather than "yesterday" in the first paragraph. I've corrected that here to avoid confusion for anyone that hasn't read the post yet. Sorry about that.
  • 3. Re: TRUNC(SYSDATE) question
    rp0428 Guru
    Currently Being Moderated
    >
    trunc(sysdate-1) returns tomorrow at midnight
    >
    Not on any of my systems it doesn't. On my systems you need to ADD 1 to sysdate, TRUNC it and then it returns TODAY (rather tonight) at midnight. ;)
  • 4. Re: TRUNC(SYSDATE) question
    rp0428 Guru
    Currently Being Moderated
    >
    trunc(BEGIN_INTERVAL_TIME)
    >
    Don't use TRUNC on table columns - that will prevent any regular index on that column from being used. An index would only be used if it was a functional index on 'TRUNC(BEGIN_INTERVAL_TIME)'.
  • 5. Re: TRUNC(SYSDATE) question
    Justin Cave Oracle ACE
    Currently Being Moderated
    rp0428 wrote:
    >
    trunc(sysdate-1) returns tomorrow at midnight
    >
    Not on any of my systems it doesn't. On my systems you need to ADD 1 to sysdate, TRUNC it and then it returns TODAY (rather tonight) at midnight. ;)
    Upon futher testing, my systems appear to work the same way. Adding numbers takes you into the future, subtracting them takes you into the past. Seems rather logical :-)

    I updated my answer to fix the error. Thanks for catching it!

    Justin
  • 6. Re: TRUNC(SYSDATE) question
    IckyIckyChiMoon Explorer
    Currently Being Moderated
    Whoops....
    Be careful Mr. Cave

    trunc(sysdate-1) returns yesterday at midnight, not tomorrow.

    Also "returns everything from the 48 hour period between midnight yesterday and 11:59:59pm today." is not accurate.
    trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)
    This will actually return a ( 24hour plus 1 second) interval's worth of data.
  • 7. Re: TRUNC(SYSDATE) question
    rp0428 Guru
    Currently Being Moderated
    >
    trunc(sysdate-1) returns yesterday at midnight, not tomorrow.
    >
    Well that sort of depends on whether your concept of 'midnight' as it relates to sysdate(today) means 'tonight' or 'this morning'. ;)

    If you TRUNC(SYSDATE) I don't get today's 'midnight tonight' I get yesterday's 'midnight tonight'. More on that after midnight (er, I mean tomorrow).
  • 8. Re: TRUNC(SYSDATE) question
    IckyIckyChiMoon Explorer
    Currently Being Moderated
    Remember that midnight is 00:00:00 not 24:60:60. Midnight is always the start of a day.
  • 9. Re: TRUNC(SYSDATE) question
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    DBA112 wrote:
    Dear SQL Experts,

    Can you clarify the difference b/w the below 2 conditions. I see same results with either of the conditions. I'm trying to pull data for 24 hours
    it's unclear if you want to use TRUNC at all. If you do, there are ways to avoid using it on every row. (Calling TRUNC as in "WHERE TRUNC (begn_interval_time) ..." is inefficient. "TRUNC (SYSDATE)" isn't nearly as bad.)

    If begin_interval_time is a DATE, and you run the query at 17:21:10 on January 14, 2013, then
    WHERE   begin_interval_time  >  SYSDATE - 1
    AND     begin_interval_time <=  SYSDATE
    will include all values of begin_inteval_time from
    17:21:11 on January 13, 2013 through
    17:21:10 on January 14, 2013.


     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
    if the results depend on when the query is run, then give a couple of different run times, and the results you want from the same sample data for each one.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}
  • 10. Re: TRUNC(SYSDATE) question
    rp0428 Guru
    Currently Being Moderated
    >
    Remember that midnight is 00:00:00 not 24:60:60. Midnight is always the start of a day.
    >
    Correct! But that is only half of it! Midnight is also always the END of a day.

    http://en.wikipedia.org/wiki/Midnight
    >
    Midnight marks the beginning and ending of each day in civil time throughout the world. It is the dividing point between one day and another.
  • 11. Re: TRUNC(SYSDATE) question
    IckyIckyChiMoon Explorer
    Currently Being Moderated
    OK, so you caught me thinking in Oracle. So, just in case, I even tried both of these (they both fail):

    select to_date( '2013-01-14 24:00:00', 'YYYY-MM-DD HH24:MI:SS') from dual;
    select to_date( '2013-01-14 23:60:60', 'YYYY-MM-DD HH24:MI:SS') from dual;
  • 12. Re: TRUNC(SYSDATE) question
    Justin Cave Oracle ACE
    Currently Being Moderated
    IckyIckyChiMoon wrote:
    Whoops....
    Be careful Mr. Cave

    trunc(sysdate-1) returns yesterday at midnight, not tomorrow.
    Yup. That one has been fixed.
    Also "returns everything from the 48 hour period between midnight yesterday and 11:59:59pm today." is not accurate.
    trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)
    This will actually return a ( 24hour plus 1 second) interval's worth of data.
    I stand by my original statement. Since you are doing a trunc(BEGIN_INTERVAL_TIME), you will return all rows where the BEGIN_INTERVAL_TIME is between midnight yesterday and 11:59:59 pm today, a 48 hour period. If SYSDATE returns January 14,
     trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)
    will return any rows where BEGIN_INTERVAL_TIME is between January 13 at 00:00:00 and January 14 at 23:59:59, a 48 hour period.

    Justin
  • 13. Re: TRUNC(SYSDATE) question
    IckyIckyChiMoon Explorer
    Currently Being Moderated
    Whoops, yup!

    BEGIN_INTERVAL_TIME between trunc(sysdate-1) and trunc(sysdate)
    .... 24 hours & 1 second


    trunc(BEGIN_INTERVAL_TIME) between trunc(sysdate-1) and trunc(sysdate)
    .... 48 hours.

    Thanks
  • 14. Re: TRUNC(SYSDATE) question
    onedbguru Pro
    Currently Being Moderated
    Be very careful when using function(col) = something. If you have an index on "col", you may be surprised as to whether or not it uses it. (hint - it generally doesn't)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points