Compare Time value

[nandanak]

I have stored transaction time in a progress DB table as a character value as : STRING(TIME,"HH:MM:SS"). How can I compare these two time values (less than , greater than time) ?.

 

[TomBascom]

Don't store such values as strings. Store them as DATETIME or DATETIME-TZ and then use the INTERVAL() function.

If you are on some ancient, obsolete and unsupported release that does not have DATETIME you will need to parse your string and manually calculate it the old fashioned way. Upgrading would be a better idea.

 

[jdpjamesp]

If you're on obsolete versions, then you can store your time as the integer value it essentially is. It's a lot more usable as an integer than a string like that.

 

[GregTomkins]

Where I work, changing the type (or anything else) of an existing DB field is more-or-less impossible, so if that's your situation, I'd just write a little function to convert the string back into seconds, eg. something like 'return (int(substr(date_field,1,2)) * 60 * 60) + (int(substr(date_field,4,2)) * 60) + int(substr(date_field,7))'. Then do the math on that. Consider the possibility of the times spanning midnight, and that everyone is in the same time zone, and that there could be unexpected (non-numeric or otherwise out of range) data in the field

 

[Pavan Yadav]

You can use the INT to covert both time and can compare it. For ex.: INT(Time1), INT(time2) etc.
This way you won't be changing anything onto DB but just evaluating the data as per your needs.

 

[TomBascom]

You can use the INT to covert both time and can compare it. For ex.: INT(Time1), INT(time2) etc.
This way you won't be changing anything onto DB but just evaluating the data as per your needs.

Actually, no, that doesn't work. The original post specifies that the data is currently stored as strings.

This code:

display integer( "10:30:15" ).

throws an error. It does not convert the time string to an integer. To convert a string like that to a time value you must parse it. In this case you might do something like:

function str2time returns integer ( input st as character ):
  define variable t as integer no-undo.
  define variable n as integer no-undo.
  n = num-entries( st, ":" ).
  assign
    t = ? when n < 1
    t = ( t + integer( entry( 1, st, ":" )) * 3600 ) when n >= 1
    t = ( t + integer( entry( 1, st, ":" )) * 60 )   when n >= 2
    t = ( t + integer( entry( 1, st, ":" )) * 1 )    when n = 3
  .
  return t.
end.

display str2time( "10:30:15" ).

 

[Stefan]

Change:

display str2time( "10:30:15" ).

to the unit test version:

display string( str2time( "10:30:15" ), "hh:mm:ss" ).

and then fix your bugs! ;-)

 

[TomBascom]

Argh!

function str2time returns integer ( input st as character ):
  define variable t as integer no-undo.
  define variable n as integer no-undo.
  n = num-entries( st, ":" ).
  assign
    t = ? when n < 1
    t = ( t + integer( entry( 1, st, ":" )) * 3600 ) when n >= 1
    t = ( t + integer( entry( 2, st, ":" )) * 60 )   when n >= 2
    t = ( t + integer( entry( 3, st, ":" )) * 1 )    when n = 3
  .
  return t.
end.

display string( str2time( "10:30:15" ), "hh:mm:ss" ).

 

[Stefan]

And now, just for curiosity's sake, nearly the same with some datetime voodoo:

FUNCTION timeToInt RETURNS INTEGER (
   i_ctime AS CHAR
):
 
   RETURN 
      INT( 
         MTIME( DATETIME( "010101T" + i_ctime ) )
         / 1000       
      ).
 
END FUNCTION.
 
 
MESSAGE STRING( timeToInt( "10:30:15" ), "hh:mm:ss"  ) VIEW-AS ALERT-BOX.