Results 1 to 5 of 5

Thread: DATE and TIME

  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: DATE and TIME

    Hi all,
    I am using DB2 ESE V7.. I am trying to find out the difference between two dates.

    I have a total of 4 columns.
    STRT_DATE (DATE),START_TIME(TIME),
    STOP_DATE(DATE),STOP_TIME(TIME)..
    START_DATE is always earliar than the STOP_DATE.
    How can I find out the time difference in hours and minutes(ex.. 36 hours 23 minutes) between START and STOP.

    Thanks in advance

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You can either use SQL to get a "time duration" by doing (see the manual on time duration):

    timestamp(stop_date,stop_time) - timestamp(start_date,start_time)

    or you can write a udf to compute the difference in whatever units you want.

    Andy

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    once u are having 2 timestamps, u can use a
    scalar function timestampdiff
    to find diff. in seconds, min., days etc.

    However look out for the asumptions made for usage of this function.
    not sure if it is present in DB2 ESE V7.

    --Rahul Singh

  4. #4
    Join Date
    Nov 2006
    Posts
    31
    Thanks,
    I cannot use timestampdiff as this query is running on MVS.
    I did use the ARWinner's suggestion to find the time duration by
    timestamp(stop_date,stop_time) - timestamp(start_date,start_time).
    How do I retreive the duration in minutes from the returned duration value.
    Thanks again...

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by samu78nyc
    timestamp(stop_date,stop_time) - timestamp(start_date,start_time).
    How do I retrieve the duration in minutes from the returned duration value.
    The returned value is a "timestamp difference", to be interpreted as follows:
    digits after the decimal point: fraction of seconds (probably to be ignored)
    two digits before decimal point: seconds (also ignore if just counting minutes)
    two digits before that: minutes (between 00 and 59)
    two digits before that: hours (between 00 and 23)
    two digits before that: number of days (in a month)
    two digits before that: number of months (hopefully equal to 0, otherwise difficult to use because of unequal month lengths ...)
    digits before that: number of years (hopefully equal to 0, otherwise difficult to use because of leap years ...)

    So you will have to calculate hours + 24 * days if result must be in hours.

    Try e.g.
    Code:
    SELECT INT(td/1000000)  * 24 +
           MOD(INT(td/10000), 100) AS hours,
           MOD(INT(td/100), 100)  AS minutes
    FROM (SELECT  timestamp(stop_date,stop_time)
                - timestamp(start_date,start_time) AS td FROM tbl) AS x
    where I'm assuming that there's not more than a month difference between the dates.
    Last edited by Peter.Vanroose; 12-03-06 at 10:42.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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