Hi, There are functions to format numbers as text times and/or dates, but I can't find equivalent ones to parse those text times/dates back to numbers.
I wanted to sum durations expressed as HH:MM:SS.SS strings, and I was expecting a function parsing such a string into a number of seconds for example, but couldn't find one. Sure you can take apart the string with substr() and cast then do the math easily via verbose SQL, but why? I ended up massaging those text durations as CSV in VIM to decompose them and then did what's below, but my question is really why the "reverse" of strftime() is not part of SQLite itself? Seems to me so "natural" it should be, I wonder if I'm not missing some obvious way to do this more easily with SQLite? Thanks, --DD sqlite> create table vs (id number primary key, hh, mm, ss, cs); sqlite> .mode csv sqlite> .import time-elapsed.txt vs sqlite> .mode col sqlite> .header on sqlite> select * from vs limit 10; id hh mm ss cs ---------- ---------- ---------- ---------- ---------- 1 00 00 02 68 14 00 00 00 78 12 00 00 02 31 4 00 00 06 36 5 00 00 08 01 8 00 00 09 36 9 00 00 09 79 11 00 00 13 62 10 00 00 17 50 33 00 00 07 86 sqlite> select id, hh||':'||mm||':'||ss||'.'||cs as "elapsed", cast(hh*(60*60)+mm*(60)+ss as number)+cs/100.0 from vs limit 10; id elapsed cast(hh*(60*60)+mm*(60)+ss as number)+cs/100.0 ---------- ----------- ---------------------------------------------- 1 00:00:02.68 2.68 14 00:00:00.78 0.78 12 00:00:02.31 2.31 4 00:00:06.36 6.36 5 00:00:08.01 8.01 8 00:00:09.36 9.36 9 00:00:09.79 9.79 11 00:00:13.62 13.62 10 00:00:17.50 17.5 33 00:00:07.86 7.86 sqlite> select id, hh||':'||mm||':'||ss||'.'||cs as "elapsed", hh*(60*60)+mm*(60)+ss+cs/100.0 from vs limit 10; id elapsed hh*(60*60)+mm*(60)+ss+cs/100.0 ---------- ----------- ------------------------------ 1 00:00:02.68 2.68 14 00:00:00.78 0.78 12 00:00:02.31 2.31 4 00:00:06.36 6.36 5 00:00:08.01 8.01 8 00:00:09.36 9.36 9 00:00:09.79 9.79 11 00:00:13.62 13.62 10 00:00:17.50 17.5 33 00:00:07.86 7.86 sqlite> select sum(hh*(60*60)+mm*(60)+ss+cs/100.0) as total from vs; total ---------- 7338.85 sqlite> select time(sum(hh*(60*60)+mm*(60)+ss+cs/100.0)) as total from vs; total ---------- 08:24:00 sqlite> select max(hh*(60*60)+mm*(60)+ss+cs/100.0) as total from vs; total ---------- 211.95 sqlite> _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
