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 ;-)