Re: time functions
A user stored times as HHMM-style values in a CHAR column (e.g. 905 = 9:05) and wanted to subtract 10 minutes in place, handling the minute/hour borrow (905 -> 855). Art Kagel suggested treating the column as an integer so the engine converts automatically, then refined the arithmetic: first a naive add, then a version using div/mod on 100 and 60 to handle wrap, and finally a tested UPDATE using trunc() and mod() (borrowing an hour, adding 60 minutes, subtracting 10, then re-normalising). He noted this is far easier done in SPL or a host language like 4GL/ESQL/C.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: > Hello! Hi. > In char field I have some data, which I would like convert to time, subtract > 10 minutes and write it back. For example: > > 905 means 9:05, --> 855 > 1415 means 14:15 --> 1405 > > How to do that? Treat the column as an integer (the engine will perform the conversions to integer and back to char automatically): update poorly_designed_table set time_char_column = (time_char_column + 10) where some_key = some_value; Art S. Kagel
On Wed, 03 Dec 2003 11:24:55 -0500, Art S. Kagel wrote: > On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: > >> Hello! > > Hi. > >> In char field I have some data, which I would like convert to time, subtract >> 10 minutes and write it back. For example: >> >> 905 means 9:05, --> 855 >> 1415 means 14:15 --> 1405 >> >> How to do that? > > Treat the column as an integer (the engine will perform the conversions to > integer and back to char automatically): > > update poorly_designed_table > set time_char_column = (time_char_column + 10) where some_key = some_value; OK, that's too simplistic as it does not deal with time wrap when the minutes exceed 60 after the calculation (BTW this is MUCH easier in an SPL routine or in a host language (ie 4GL, ESQL/C, etc)): update poorly_designed_table set time_char_column = ((time_char_column/100)*100 + (((time_char_column mod 100 + 10) / 60) * 100) + (time_char_column mod 100 + 10) mod 60) where some_key = some_value; > Art S. Kagel
On Wed, 03 Dec 2003 11:24:55 -0500, "Art S. Kagel" <kagel@bloomberg.net> wrote: >On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: > >> Hello! > >Hi. > >> In char field I have some data, which I would like convert to time, subtract >> 10 minutes and write it back. For example: >> >> 905 means 9:05, --> 855 >> 1415 means 14:15 --> 1405 >> >> How to do that? > >Treat the column as an integer (the engine will perform the conversions to >integer and back to char automatically): > >update poorly_designed_table >set time_char_column = (time_char_column + 10) >where some_key = some_value; > How would this handle scenario 1? 905 - 10 = 895
On Wed, 03 Dec 2003 11:31:30 -0500, Art S. Kagel wrote: > On Wed, 03 Dec 2003 11:24:55 -0500, Art S. Kagel wrote: > >> On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: >> >>> Hello! >> >> Hi. Sorry, went to the Simon and Garfunkle concert last night and I'm not all here. You said subtract and I did add. In case you cannot do the formula interpolation see below: >>> In char field I have some data, which I would like convert to time, subtract >>> 10 minutes and write it back. For example: >>> >>> 905 means 9:05, --> 855 >>> 1415 means 14:15 --> 1405 >>> >>> How to do that? >> >> Treat the column as an integer (the engine will perform the conversions to >> integer and back to char automatically): >> >> update poorly_designed_table >> set time_char_column = (time_char_column + 10) where some_key = some_value; > > OK, that's too simplistic as it does not deal with time wrap when the minutes > exceed 60 after the calculation (BTW this is MUCH easier in an SPL routine or > in a host language (ie 4GL, ESQL/C, etc)): > > update poorly_designed_table > set time_char_column = > ((time_char_column/100)*100 + (((time_char_column mod 100 + 10) / 60) * 100) > + (time_char_column mod 100 + 10) mod 60) > where some_key = some_value; AND FOR SUBTRACTION: update poorly_designed_table set time_char_column = (((time_char_column/100)-1)*100 + (((((time_char_column mod 100 +60) - 10) / 60) * 100) + ((time_char_column mod 100 + 60) - 10) mod 60) where some_key = some_value; It's a bit kludgy, but the gist is: - Assume the minutes are < 10 so subtract 1 hour and add 60 minutes - Subtract the 10 minutes and adjust exactly as in the addition example adding back an hour if the subtraction results in a value > 60. Art S. Kagel
On Wed, 03 Dec 2003 12:29:01 -0500, "Art S. Kagel" <kagel@bloomberg.net> wrote: >On Wed, 03 Dec 2003 11:31:30 -0500, Art S. Kagel wrote: > >> On Wed, 03 Dec 2003 11:24:55 -0500, Art S. Kagel wrote: >> >>> On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: >>> >>>> Hello! >>> >>> Hi. > >Sorry, went to the Simon and Garfunkle concert last night and I'm not all here. > You said subtract and I did add. In case you cannot do the formula >interpolation see below: > Ah, that explains it . . . .8-)
On Wed, 03 Dec 2003 12:29:01 -0500, Art S. Kagel wrote: OK one last try with correct syntax and the appropriate formatting functions to return the desired result: update timetest set time_char_column = trunc( (trunc(((time_char_column / 100) - 1),0) * 100 + (trunc((((mod(time_char_column, 100) + 60) - 10) / 60),0) * 100) + mod(((mod(time_char_column, 100) + 60) - 10),60)), 0); Tested and certified this time! Art S. Kagel > On Wed, 03 Dec 2003 11:31:30 -0500, Art S. Kagel wrote: > >> On Wed, 03 Dec 2003 11:24:55 -0500, Art S. Kagel wrote: >> >>> On Wed, 03 Dec 2003 06:42:30 -0500, Mitja Udovc wrote: >>> >>>> Hello! >>> >>> Hi. > > Sorry, went to the Simon and Garfunkle concert last night and I'm not all > here. > You said subtract and I did add. In case you cannot do the formula > interpolation see below: > >>>> In char field I have some data, which I would like convert to time, >>>> subtract 10 minutes and write it back. For example: >>>> >>>> 905 means 9:05, --> 855 >>>> 1415 means 14:15 --> 1405 >>>> >>>> How to do that? >>> >>> Treat the column as an integer (the engine will perform the conversions to >>> integer and back to char automatically): >>> >>> update poorly_designed_table >>> set time_char_column = (time_char_column + 10) where some_key = some_value; >> >> OK, that's too simplistic as it does not deal with time wrap when the minutes >> exceed 60 after the calculation (BTW this is MUCH easier in an SPL routine or >> in a host language (ie 4GL, ESQL/C, etc)): >> >> update poorly_designed_table >> set time_char_column = >> ((time_char_column/100)*100 + (((time_char_column mod 100 + 10) / 60) * >> 100) >> + (time_char_column mod 100 + 10) mod 60) >> where some_key = some_value; > > AND FOR SUBTRACTION: > > update poorly_designed_table > set time_char_column = > (((time_char_column/100)-1)*100 > + (((((time_char_column mod 100 +60) - 10) / 60) * 100) + > ((time_char_column mod 100 + 60) - 10) mod 60) > where some_key = some_value; > > It's a bit kludgy, but the gist is: > - Assume the minutes are < 10 so subtract 1 hour and add 60 minutes - Subtract > the 10 minutes and adjust exactly as in the addition example adding back an > hour if the subtraction results in a value > 60. > > Art S. Kagel