Skip to Content
avatar image
Former Member

FINDING DATETIME DIFFERENCE

Hi

I have an issue. I have two datetime fields dt1 and dt2. I require the difference between these two.

I used the SecondsAfter Function. It is working ok when I am obtaining the difference between the time on any one day.

But suppose dt1 is '30/12/2014 23:30' and dt2 is '31/12/2014 00:30' then how to obtain the difference?.

Regards

JAIMOHAN

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    avatar image
    Former Member
    Dec 30, 2014 at 05:12 AM

    Try this function:

    decimal ll_hours

    //ll_hours = DaysAfter (date (ldt_1), date (ldt_2)) * 24.0 + SecondsAfter(time (ldt_1), time (ldt_2)) / 3600.0

    ll_hours = DaysAfter (date (ldt_2), date (ldt_1)) * 24.0 + SecondsAfter(time (ldt_2), time (ldt_1)) / 3600.0

    return ll_hours

    You may have to experiment to figure out which inputs to use in which order.

    Paul

    P.S.:  Could SOME PERSON WHO ACTS AS MODERATOR AND USUALLY TAKES DELIGHT IN DELETING MY POST PLEASE RESTORE MY ACCOUNT WITH EMAIL ADDRESS pemurray@interrasys.com SO IT IS NO LONGER ASSOCIATED WITH SOMEONE WITH INTIALS T.R.? 

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 30, 2014 at 10:04 PM

    Good Olde PFC has a function:

    //////////////////////////////////////////////////////////////////////////////

    //

    // Function:   of_SecondsAfter

    //

    // Access:   public

    //

    // Arguments:

    // adtm_start Beginning time.

    // adtm_end   Ending time.

    //

    // Returns:   long

    // Number of whole seconds between two date times.

    // If any argument's value is NULL, function returns NULL.

    // If any argument's value is Invalid, function returns NULL.

    //

    // Description:   Given two datetimes, return the number of seconds between

    // the two.

    //

    //////////////////////////////////////////////////////////////////////////////

    //

    // Revision History

    //

    // Version

    // 5.0   Initial version

    //

    //////////////////////////////////////////////////////////////////////////////

    //

    /*

    * Open Source PowerBuilder Foundation Class Libraries

    *

    * Copyright (c) 2004-2005, All rights reserved.

    *

    * Redistribution and use in source and binary forms, with or without

    * modification, are permitted in accordance with the GNU Lesser General

    * Public License Version 2.1, February 1999

    *

    * http://www.gnu.org/copyleft/lesser.html

    *

    * ====================================================================

    *

    * This software consists of voluntary contributions made by many

    * individuals and was originally based on software copyright (c)

    * 1996-2004 Sybase, Inc. http://www.sybase.com.  For more

    * information on the Open Source PowerBuilder Foundation Class

    * Libraries see http://pfc.codexchange.sybase.com

    */

    //

    //////////////////////////////////////////////////////////////////////////////

    long ll_total_seconds, ll_day_adjust

    date ld_sdate, ld_edate

    time lt_stime, lt_etime

    //Check parameters

    If IsNull(adtm_start) or IsNull(adtm_end) or &

      Not of_IsValid(adtm_start) or Not of_IsValid(adtm_end) Then

      long ll_null

      SetNull(ll_null)

      Return ll_null

    End If

    ld_sdate = date(adtm_start)

    ld_edate = date(adtm_end)

    lt_stime = time(adtm_start)

    lt_etime = time(adtm_end)

    //Note: 86400 is number of seconds in a day.

    If ld_sdate = ld_edate then

      ll_total_seconds = secondsafter( lt_stime,lt_etime)

    Elseif ld_sdate < ld_edate Then

      ll_total_seconds = SecondsAfter(lt_stime,Time('23:59:59'))

      ll_day_adjust = DaysAfter(ld_sdate,ld_edate) -1

      If ll_day_adjust > 0 Then ll_total_seconds = ll_total_seconds + 86400 * ll_day_adjust

      ll_total_seconds = ll_total_seconds + SecondsAfter(Time('00:00:00'),lt_etime) +1

    Else //end date < start date

      ll_total_seconds = SecondsAfter(lt_stime,Time('00:00:00'))

      ll_day_adjust = DaysAfter(ld_sdate,ld_edate) +1

      If ll_day_adjust < 0 Then ll_total_seconds = ll_total_seconds + 86400 * ll_day_adjust

      ll_total_seconds = ll_total_seconds + SecondsAfter(Time('23:59:59'),lt_etime) -1

    end If

    return ll_total_seconds

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 30, 2014 at 12:22 PM

    Depending upon your situation it may be easier to let the database handle these sorts of things.  In MS SQLserver you can do something like

    sqlstatement = 'select DateDiff(mi, "' + string(ldt_start) + '", "' + string(ldt_stop) + '")'PREPARE SQLSA FROM :sqlstatement ;OPEN DYNAMIC get_time_diff ;FETCH get_time_diff INTO :ll_dur_qy ;CLOSE get_time_diff ;

    You can change the time difference designator ('mi' in the above example) to seconds, days, weeks, etc.

    In any event you may need to handle 'fat fingers' in your input prior to obtaining the date difference. (someone enters 1014 as a start year instead of 2014 for example).

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Dec 30, 2014 at 09:48 PM

    I don't understand why PowerBuilder can't have a simple built in function for this. It's a big pain to have to some sort of grand calculation to get the difference between two datetime values. Hitting the database just to do that is also annoying.Oh well, one can only dream...

    Add comment
    10|10000 characters needed characters exceeded