On Nov 19, 2007, at 10:59 PM, Igor Tandetnik wrote:
Jonathan O <[EMAIL PROTECTED]> wrote:
On 11/19/07, Igor Tandetnik <[EMAIL PROTECTED]>
wrote:
What does the data in time_column look like?
Currently looks like HH:MM:SS or MM:SS.
The latter is going to cause difficulties - SQLite's date/time
functions are going to interpret it as HH:MM with seconds omitted.
If you insist on storing strings, normalize them all to HH:MM:SS
format. Then you can do something like this:
select time(AVG(strftime('%s', time_column)) / 86400.0 - 0.5)
from tablename
This did the trick.
You can also normalize on the fly, but that'll make for a rather
horrific-looking statement:
select time(AVG(strftime('%s',
case when length(time_column) < 8 then '00:' || time_column else
time_column end
)) / 86400.0 - 0.5)
from tablename;
Didn't know there was a length() function!! Learned something new!
I think this is the answer I was needing. But how do I convert
seconds to HH:MM:SS?
select time(numSeconds / 86400.0 - 0.5);
Ahh a derivative of the above calculations.
Thanks!
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
