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;
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;
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);
See also
http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
