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! Many thanks, Michael Billimoria
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 ;-)
participants (2)
-
320011178755-0001@t-online.de -
Michael Billimoria