You mean like this:
create table test
(
value text not null
);
insert into test values ('00:00:02.68');
insert into test values ('00:00:00.78');
insert into test values ('00:00:02.31');
insert into test values ('00:00:06.36');
insert into test values ('00:00:08.01');
insert into test values ('00:00:09.36');
insert into test values ('00:00:09.79');
insert into test values ('00:00:13.62');
insert into test values ('00:00:17.50');
insert into test values ('00:00:07.86');
select value,
round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
from test;
value round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
----------- --------------------------------------------------------------
00:00:02.68 2.68
00:00:00.78 0.78
00:00:02.31 2.31
00:00:06.36 6.36
00:00:08.01 8.01
00:00:09.36 9.36
00:00:09.79 9.79
00:00:13.62 13.62
00:00:17.50 17.5
00:00:07.86 7.86
You are limited to "value" between 00:00:00.000 and 23:59:59.999 since the
internal datetime only stores julian milliseconds. Note that the default date
if you do not provide that part is 2000-01-01
--
The fact that there's a Highway to Hell but only a Stairway to Heaven says a
lot about anticipated traffic volume.
>-----Original Message-----
>From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf
>Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 02:36
>To: General Discussion of SQLite Database <sqlite-
>us...@mailinglists.sqlite.org>
>Subject: [sqlite] Why aren't there date/time parsing built-in functions in
>SQLite
>
>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
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
