Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    7

    Getting timestamp difference in minutes

    Hi,
    I am not that proficient with db2.
    I want to query a table having timestamp column say for the past 150 minutes. I am not aware of how do I get this in the SQL query? Could you respond back. I know current timestamp -2 hours will work. But I want the difference in minutes. Is it possible in db2?
    Thx
    Shrinivas

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649

    Re: Getting timestamp difference in minutes

    There is a function TIMESTAMPDIFF documented in SQL Reference ... Have a look at it

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Well, it is possible, but no straight forward

    For example:

    SELECT DIGITS(INTEGER(
    CURRENT TIMESTAMP - (CURRENT TIMESTAMP - 2 HOURS - 10 MINUTES)))
    FROM SYSIBM.SYSDUMMY1

    will return 0000021000 meaning 2 hours ten minutes 0 seconds. so you can compute to number of minutes by using the substr-function

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,427

    Re: Getting timestamp difference in minutes

    Originally posted by shrinibv1
    Hi,
    I am not that proficient with db2.
    I want to query a table having timestamp column say for the past 150 minutes. I am not aware of how do I get this in the SQL query? Could you respond back. I know current timestamp -2 hours will work. But I want the difference in minutes. Is it possible in db2?
    Thx
    Shrinivas
    What exactly is your problem? Have you tried "CURRENT TIMESTAMP - 150 MINUTES"?

    db2 => values(current timestamp)

    1
    --------------------------
    2004-02-05-09.47.40.718001

    1 record(s) selected.

    db2 => values(current timestamp - 150 minutes)

    1
    --------------------------
    2004-02-05-07.17.48.343001

    1 record(s) selected.

  5. #5
    Join Date
    Nov 2003
    Location
    Germany
    Posts
    62
    Sathyaram

    TIMESTAMPDIFF is not a function on the OS/390 platform.

  6. #6
    Join Date
    Feb 2004
    Posts
    7
    Hi,
    Thx Walter.
    It works fine.
    Thanks again
    Shrini
    Originally posted by Walter Janissen
    Well, it is possible, but no straight forward

    For example:

    SELECT DIGITS(INTEGER(
    CURRENT TIMESTAMP - (CURRENT TIMESTAMP - 2 HOURS - 10 MINUTES)))
    FROM SYSIBM.SYSDUMMY1

    will return 0000021000 meaning 2 hours ten minutes 0 seconds. so you can compute to number of minutes by using the substr-function

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,649
    Thanks Walter for pointing out that we do not have that function in OS/390...



    Originally posted by Walter Janissen
    Sathyaram

    TIMESTAMPDIFF is not a function on the OS/390 platform.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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