[Zope] Treating 00:00 as hh:mm

Andreas Heckel andreas@easyleading.org
Fri, 24 Aug 2001 10:42:43 +0200


Michael Billimoria wrote:
> 
> Hi there,
> 
> I'm having a minor problem with an SQL statement that's being called
> from Zope to add up some times.
> 
> Basically times are entered into a database field called "timetaken" in
> the format of hh:mm
> 
> I am trying to compile a report to add up the times and give them back
> in the format of hh:mm
> 
> Unfortunately using SUM(timetaken) decides to truncate the characters
> after the ":" and not add them up as hours.
> 
> Similarly using the SQL REPLACE string command to remove the ":" will
> then proceed to add the values as decimal values.
> 
> Is there a simple way? The column itself is defined as a varchar
> currently, I've tried defining it as time, but it doesn't seem to work
> properly as it takes time as being clock time (hh:mm:ss AM/PM)
> 
> Hope someone can help!

Define a db field called duration as integer and convert your input 
from HH:MM to minutes.

Create a python skript called convert 
Parameter list: input

hhmm=string.split(string.strip(input),':')
return string.atoi(hhmm[0])*60+string.atoi(hhmm[1])

If you like to insert a duration of 61 min in your db you can use
this python skrikt in your ZSqlMethod

insert into yourtable values(duration=<dtml-var "convert('01:01')">)



-- 
_______________________________________________________________________
Andreas Heckel                                  andreas@easyleading.org
LINUX is like a wigwam...no gates...no windows and an apache inside ;-)