Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2011
    Posts
    7

    Unanswered: Query to retrieve previous day data

    Hi,

    I would like to have a query which should fetch previous day records from column which is having timestamp data type.

    select mdn from user_table where updatetimestamp > trunc(sysdate) - INTErVAL '24' HOUR;

    But this gives output not for previous day, but all records which are 24 hrs less than current day. Any suggestion on how to get records for previous day based on column having timestamp data type.

    Thank you in advance.

    Cheers,
    Sudduble

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    select mdn from user_table where trunc(updatetimestamp) = trunc(sysdate-1)
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Nov 2011
    Posts
    7
    Thank you for the reply.

    2610: Operand data type 'TIMESTAMP' invalid for operator 'TRUNC' in expr (user_table.UPDATETIMESTAMP)
    The command failed.

    I get above error.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post results from following SQL

    SELECT * FROM V$VERSION;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2011
    Posts
    7
    Am using Oracle Timesten database, the query results in table <user>.v$version not found...Thanks..

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    this is NOT a Timesten forum.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Nov 2011
    Posts
    7
    np. Have moved this question to timesten forum. Thanks

  8. #8
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Code:
    select mdn from user_table where updatetimestamp > trunc(sysdate) - 1;
    Still don't know if it will work with TimesTen, but at least you don't have to do a trunc on the timestamp.

    Oracle dates are actually numbers where 1 = 24 hours, i.e. 1/24 = 1 hour. So you can use that to do arithmetic on date values.
    Code:
    trunc(sysdate) = this morning at 00:00:00
    trunc(sysdate) + (1/24) = 1am this morning
    trunc(sysdate) + (23.5/24) = 23h30 tonight
    etc. etc.

  9. #9
    Join Date
    Nov 2011
    Posts
    7
    Thank you.. :-)

Posting Permissions

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