Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: datetime

  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: datetime

    Hi

    Running Oracle 9i.

    How can I select data for the time period 8am to 11am on the day?

    Regards
    Shajju

  2. #2
    Join Date
    Aug 2008
    Posts
    464

    Example

    This should give me one hour's data (the hour being 8hrs ago), right?

    WHERE DATETIME between trunc(sysdate,'hh')-(8/24) and trunc(sysdate,'hh')-(8/24/3600)

    but instead it gave me 8 hrs data starting 8hrs ago.

    I want to select data for the period 8am to 11am. Please help me clear my concept?

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    In other words, can I select a particular time of the day say 9am at any time of the day (without having to use sysdate)?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    This should give me one hour's data (the hour being 8hrs ago), right?

    WHERE DATETIME between trunc(sysdate,'hh')-(8/24) and trunc(sysdate,'hh')-(8/24/3600)

    but instead it gave me 8 hrs data starting 8hrs ago.
    Is this not what you wanted? (the data from 8hrs ago till 8 seconds before start of this hour).
    Quote Originally Posted by shajju
    I want to select data for the period 8am to 11am. Please help me clear my concept?
    So do it.
    TRUNC(sysdate,'DD') will give you start of the day.
    8/24 will give you 8 hours, 11/24 will give you 11 hours. Simply add them.
    Quote Originally Posted by shajju
    In other words, can I select a particular time of the day say 9am at any time of the day (without having to use sysdate)?
    Again seems to be slightly different requirement.
    Anyway, examine <datetime> - TRUNC( <datetime>, 'DD' ) (= the difference between the datetime and start of its day).

  5. #5
    Join Date
    Aug 2008
    Posts
    464

    Thanks

    Appreciated

  6. #6
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    From the question, I'm not clear what you are trying to accomplish. But..

    to_char(datetime, 'SSSSS') will give you seconds past midnight.
    to_char(datetime, 'HH24') or extract(hour FROM from datetime) will give you the hours.

    So for instance, if you wanted to see what your sales were between 8am and 11am for a week you might write a query like so.

    Code:
    SELECT *
    FROM sales
    WHERE checkout_time >= to_date('2009-09-13', 'YYYY-MM-DD')
      AND checkout_time  < to_date('2009-09-19', 'YYYY-MM-DD')
      AND to_char(checkout_time, 'HH24') BETWEEN '08' AND '11'

  7. #7
    Join Date
    Aug 2008
    Posts
    464
    Thanks a lot.

  8. #8
    Join Date
    Aug 2008
    Posts
    464
    Hi

    I used

    Code:
    WHERE DATETIME between TRUNC(SYSDATE-1,'DD')+(0/24) and TRUNC(SYSDATE-1,'DD')+(11/24)
    This returns 12 rows when I group by datetime.

    but +(11/24) does not mean 11:59:59. Need 11:59:59.

    I tried changing +(11/24) to +(11/24/3600) but I only get 1 row returned for 00:00:00 hours. Why does adding 3600 reduce the output to just one row?

    Concepts not clear.
    Last edited by shajju; 09-15-09 at 10:19.

  9. #9
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    TRUNC(SYSDATE-1,'DD')+(0/24)
    Do you under stand what you are doing here? or here '+(11/24) to +(11/24/3600)'?

    You are starting with midnight of yesterday (SYSDATE - 1) and then you are adding the fractional equivalent of hours or seconds. I would suggest you get rid of any occurrences of n/24/3600 and replace with n/86400 so you can make the mental connection that with n/24 you are working with hours and with n/86400 you are working with seconds. So 11/24/3600 is not 11:00:00 but 00:00:11.


    but +(11/24) does not mean 11:59:59. Need 11:59:59.
    Instead of BETWEEN use < TRUNC(SYSDATE - 1, 'DD') + 1/2. But if you really want to use between then get out your calculator 86400 / 2 - 1.

  10. #10
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by artacus72
    You are starting with midnight of yesterday (SYSDATE - 1)
    Not entirely true. SYSDATE - 1 (or any DATE for that matter) is exactly 24 hours back, there is no "rounding" to midnight involved here:
    Code:
    SQL*Plus: Release 10.2.0.1.0 - Production on Tue Sep 15 20:11:09 2009
    
    Copyright (c) 1982, 2005, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the OLAP and Data Mining options
    
    SQL> alter session set nls_date_format='dd.mm.yyyy hh24:mi:ss';
    
    Session altered.
    
    SQL> select sysdate, sysdate - 1 from dual;
    
    SYSDATE             SYSDATE-1
    ------------------- -------------------
    15.09.2009 20:11:24 14.09.2009 20:11:24
    
    SQL>

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    To answer your original question see the following two queries. the first goes from 8am to 11am. The second goes from 8am to 11pm.


    Code:
    >select trunc(sysdate)+8/24,trunc(sysdate)+11/24 from dual;
    
    TRUNC(SYSDATE)+8/24 TRUNC(SYSDATE)+11/2
    ------------------- -------------------
    15.09.2009 08:00:00 15.09.2009 11:00:00
    
    >select trunc(sysdate)+8/24,trunc(sysdate)+23/24 from dual
    
    TRUNC(SYSDATE)+8/24 TRUNC(SYSDATE)+23/2
    ------------------- -------------------
    15.09.2009 08:00:00 15.09.2009 23:00:00
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Aug 2008
    Posts
    464

    sysdate

    Thanks for all your replies..

    Could someone please explain the difference between:

    trunc(sysdate,'dd') and trunc(sysdate,'hh')

    I got different results with:

    WHERE DATETIME between trunc(sysdate,'DD')-1/24 and trunc(sysdate,'DD')-1/24/3600

    and

    WHERE DATETIME between trunc(sysdate,'HH')-1/24 and trunc(sysdate,'HH')-1/24/3600
    Last edited by shajju; 09-16-09 at 03:50.

  13. #13
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by shajju
    Could someone please explain the difference between:

    trunc(sysdate,'dd') and trunc(sysdate,'hh')

    Doesn't 'trunc' mean truncate to 00:00hrs?
    Maybe you will be surprised, but TRUNC function (as many other Oracle SQL constructions) is perfectly described in SQL Reference book. It is a part of Oracle documentation, available e.g. online on http://tahiti.oracle.com/
    For 10gR2, its definition is here: http://download.oracle.com/docs/cd/B...201.htm#i79761
    The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is truncated to the nearest day. Please refer to "ROUND and TRUNC Date Functions" for the permitted format models to use in fmt.

  14. #14
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Not entirely true. SYSDATE - 1 (or any DATE for that matter) is exactly 24 hours back, there is no "rounding" to midnight involved here:
    I'm well aware of how date math works in Oracle. There was a TRUNC in my example to round to midnight. I was trying to explain to OP that he is subtracting 1 for a whole day then adding some fraction of a day.

  15. #15
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This is the third thread in this forum by the same person asking the same question.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •