Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: CURRENT TIMESTAMP help in sql

    Currently we have something that runs daily via cron that uses the below code for the timestamp

    and timestamp(a.SYSTEM_DATETIME)>=timestamp('${DQUERYB EGIN} 00:00:00') and timestamp(a.SYSTEM_DATETIME)<=timestamp('${DQUERYB EGIN} 23:59:59') and a.ticket_id=b.ticket_id

    This was written by someone who is no longer with us and I have been asked to run this more frequently so that the data is "fresh" in the report. I was looking up CURRENT TIMESTAMP but no sure how to put in use with the above (the above works but as you can see it uses the 24 hour clock and only shows data from the previous day. The goal here is to run the same job twice daily covering 12 hour intervals but I keep getting errors pertaining the timstamp.

    any help would be appreciated

    thanks

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    CURRENT TIMESTAMP - MICROSECOND(CURRENT TIMESTAMP) MICROSECONDS - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS

    and

    CURRENT TIMESTAMP - MICROSECOND(CURRENT TIMESTAMP) MICROSECONDS - MIDNIGHT_SECONDS(CURRENT TIMESTAMP) SECONDS + 12 HOURS
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jul 2016
    Location
    Germany
    Posts
    32
    Provided Answers: 2

    quick and dirty

    Hi mark_maz,

    you didn't specify the environment.
    Please take a look at "Must Read before posting".

    This little snipplet is not much information
    for a qualified answer, so let's guess:

    a.SYSTEM_DATETIME might be a columns of a db2 table
    specified by something like SELECT COLUMN FROM TABLE a

    ${DQUERYB EGIN} looks like a reference to some other part of you cron job
    Since you mention the output to be the previous days data,
    ${DQUERYB EGIN} would deliver yesterday

    >= 00:00:00 <= 23:59:59 gives a 24 hours interval

    To modify this you change the intervall and use some different ${DQUERYB EGIN}

    Good luck
    db2dp

Posting Permissions

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