Also, note that you have to use the 'unixepoch' modifier with the time function
so that it knows the value is seconds, not days, since floats are by default
days and integers are by default seconds. The 'unixepoch' modifier tells the
internal datetime functions that the provided value is relative to the unix
epoch in seconds, rather than the julian epoch in days. I don't think there is
a modifier to force the days from the julian epoch interpretation.
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');
.mode col
.head on
select value,
round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
from test;
select sum(round((julianday(value) - julianday('00:00:00')) * 86400.0, 3))
from test;
select time(sum(round((julianday(value) - julianday('00:00:00')) * 86400.0,
3)), 'unixepoch')
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
sum(round((julianday(value) - julianday('00:00:00')) * 86400.0, 3))
-------------------------------------------------------------------
78.27
time(sum(round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)),
'unixepoch')
--------------------------------------------------------------------------------------
00:01:18.270
sqlite> select time(7338.85);
time(7338.85)
-------------
08:24:00.000
sqlite> select time(7338.85, 'unixepoch');
time(7338.85, 'unixepoch')
--------------------------
02:02:18.850
--
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: Keith Medcalf <kmedc...@dessus.com>
>Sent: Tuesday, 10 September, 2019 05:05
>To: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
>Subject: RE: [sqlite] Why aren't there date/time parsing built-in functions
>in SQLite
>
>
>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
