Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Oct 2010
    Posts
    49

    Unanswered: Select records greater than SYSDATE + 3 hours

    Hi,

    Im having trouble figuring out how to return records from the database, using the sysdate function.

    I can use the code below when I want to return records with a sysdate less x number of days. This works fines.

    Code:
    SELECT * FROM TABLE1
          WHERE  TIMESTAMP > To_Char(Trunc(SYSDATE-x),'YYYYMMDD')
    However I struggling to work out how to return all records that were created today after 03:00 hours, the below is what I have, but it just returns all records from today.


    Code:
      SELECT * FROM TABLE1
          WHERE  TIMESTAMP > To_Char(Trunc(SYSDATE+0.0300),'YYYYMMDDHH24MI')
    Can anyone point me in the right direction?

    Many Thanks

    Zig

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Did you try:
    Code:
    SELECT * 
    FROM TABLE1
    WHERE TIMESTAMP > trunc(SYSDATE) + INTERVAL '3' HOUR
    Last edited by shammat; 10-14-10 at 09:00. Reason: Added call to trunc() to match the original query

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    In date arithmetics, 1 means 1 day. As 1 day contains 24 hours, 1 hour is equal to 1/24. So 3 hours = 3/24. Do not be afraid to use fractions, Oracle can handle it well.

    Also, please note the difference between these expressions:
    Code:
    TRUNC(sysdate+3/24)
    TRUNC(sysdate)+3/24
    The first returns today's midnight when it is before 21:00; otherwise it returns the midnight of the last day.
    The second returns today's 3:00 in the morning.

    Just curious: has TABLE1.TIMESTAMP column CHAR/VARCHAR2 data type?
    Because, if TABLE1.TIMESTAMP had DATE/TIMESTAMP data type, using TO_CHAR is superfluous - you should only SYSDATE there without any TO_CHAR conversion.

  4. #4
    Join Date
    Oct 2010
    Posts
    49
    Thank you for both your reponses, they both work.

    I had tried using fractions but had my ')' in the wrong place as highlighted. I'm new to all this, a slightly misplace comma here or character there and you get nothing!


    Just curious: has TABLE1.TIMESTAMP column CHAR/VARCHAR2 data type?
    Because, if TABLE1.TIMESTAMP had DATE/TIMESTAMP data type, using TO_CHAR is superfluous - you should only SYSDATE there without any TO_CHAR conversion.
    To be honest I've no idea, it's not my database, I only have select access to it. Is there a select query I could run to determine how the column is formated?

    If it helps the timestamp column contains this many characters
    '2010101409000005' Im ok with YYYYMMDDHHMISS, do the last two characters also represent the time?

    Thanks Again,as always Im amazed at the speed of response from this site, it's excellent.

    Zig

  5. #5
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by Ziggadebo View Post
    To be honest I've no idea, it's not my database, I only have select access to it. Is there a select query I could run to determine how the column is formated?
    A timestamp (or date) column does not have a "format".

  6. #6
    Join Date
    Oct 2010
    Posts
    49
    Excuse my error I meant data type?

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Ziggadebo View Post
    To be honest I've no idea, it's not my database, I only have select access to it. Is there a select query I could run to determine how the column is formated?
    To be honest, as you are using it, you definitely should know it for creating correct queries.
    Quote Originally Posted by Ziggadebo View Post
    If it helps the timestamp column contains this many characters
    '2010101409000005' Im ok with YYYYMMDDHHMISS, do the last two characters also represent the time?
    Who knows except the one who created/documented it? It might be direct storage (VARCHAR2 data type) or just the string representation of DATE data type. There might be better options for your tool (e.g. in TOAD, simply list "Columns" tab on that table), anyway this query could give it too:
    Code:
    SELECT data_type, data_length
    FROM user_tab_columns
    WHERE table_name = 'TABLE_1' AND column_name = 'TIMESTAMP';

  8. #8
    Join Date
    Oct 2010
    Posts
    49
    Quote Originally Posted by flyboy View Post
    To be honest, as you are using it, you definitely should know it for creating correct queries.

    Who knows except the one who created/documented it? It might be direct storage (VARCHAR2 data type) or just the string representation of DATE data type. There might be better options for your tool (e.g. in TOAD, simply list "Columns" tab on that table), anyway this query could give it too:
    Code:
    SELECT data_type, data_length
    FROM user_tab_columns
    WHERE table_name = 'TABLE_1' AND column_name = 'TIMESTAMP';
    That query returns nothing. Querying just against the user_tab_columns table also returns nothing.

    Thanks

  9. #9
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Ziggadebo View Post
    That query returns nothing. Querying just against the user_tab_columns table also returns nothing.
    Well, it should be 'TABLE1' (without underscore), or the real table name if you changed it in your post.
    If there is nothing in USER_TAB_COLUMNS (columns owned by current user), try to query ALL_TAB_COLUMNS (columns accessible by current user).

  10. #10
    Join Date
    Oct 2010
    Posts
    49
    Quote Originally Posted by flyboy View Post
    Well, it should be 'TABLE1' (without underscore), or the real table name if you changed it in your post.
    If there is nothing in USER_TAB_COLUMNS (columns owned by current user), try to query ALL_TAB_COLUMNS (columns accessible by current user).
    oK all_TAB_COLUMNS worked.

    Its CHAR 20

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    You have no control over someone elses database, but as a life lesson one of the worst things you can do in table design is to use a string to store a date column. Always use a date or timestamp column, it allows you flexibility in usage.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Ziggadebo View Post
    Its CHAR 20
    OK, then use TO_CHAR with proper mask. Ask responsible people what numbers in that column mean. And do not be surprised when getting data from October, 32nd (or from 50 hours, 80 minutes) - yes, the data type is not properly chosen.

  13. #13
    Join Date
    Oct 2010
    Posts
    49
    Quote Originally Posted by beilstwh View Post
    You have no control over someone elses database, but as a life lesson one of the worst things you can do in table design is to use a string to store a date column. Always use a date or timestamp column, it allows you flexibility in usage.

    Don't get me started all the numerical columns have been created as CHAR also!

    Like I said, I don't own the db, I just have access to query against it!

  14. #14
    Join Date
    Oct 2010
    Posts
    49
    I'm expecting this code

    Code:
    To_Char(Trunc(SYSDATE-2)+22/24,'yyyymmddhhmm'
    For Ref: Today is 27/10 it's 14:38

    to return any records with the date 25/10/2010 and timestamp greater than 10pm(2200 hours), instead it's return 25/10/2010 after 10am(1000 hours)

    Where am I going wrong?

    Thanks

  15. #15
    Join Date
    Oct 2010
    Posts
    49
    Ok all sorted I was missing the 24 in the hhmm bit. now reads

    To_Char(Trunc(SYSDATE-2)+22/24,'yyyymmddhh24mi'

    and works great.

    Thanks.
    Last edited by Ziggadebo; 10-27-10 at 11:13.

Posting Permissions

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