On Tue, Sep 10, 2019 at 4:35 PM Igor Tandetnik <i...@tandetnik.org> wrote:
> On 9/10/2019 7:05 AM, Keith Medcalf wrote:
> > select value,
> > round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
> > from test;
>
> Another possibility: strftime('%s', '1970-01-01 ' || value)
>
I ended up needing %f, an implicit cast via 0.0+, and using Keith's trick
of time(..., 'unixepoch') to get the correct result. Thank you both. --DD
PS: Still... Dealing with time/date/duration shouldn't be a puzzle/quiz
like this IMHO.
D:\pdgm>sqlite3 time-elapsed-dxo.db
SQLite version 3.28.0 2019-04-16 19:49:53
Enter ".help" for usage hints.
sqlite> create table dur(id number primary key, elapsed text not null);
sqlite> .mode csv
sqlite> .import time-elapsed-dxo.txt dur
sqlite> .mode col
sqlite> .header on
sqlite> select count(*) from dur;
count(*)
----------
158
sqlite> select * from dur limit 5;
id elapsed
---------- -----------
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
sqlite> select *, strftime('%f', '1970-01-01 ' || elapsed) from dur limit 5;
id elapsed strftime('%f', '1970-01-01 ' || elapsed)
---------- ----------- ----------------------------------------
1 00:00:02.68 02.680
14 00:00:00.78 00.780
12 00:00:02.31 02.310
4 00:00:06.36 06.360
5 00:00:08.01 08.010
sqlite> select *, 0.0+strftime('%f', '1970-01-01 ' || elapsed) from dur
limit 1;
id elapsed 0.0+strftime('%f', '1970-01-01 ' || elapsed)
---------- ----------- --------------------------------------------
1 00:00:02.68 2.68
sqlite> select *, typeof(0.0+strftime('%f', '1970-01-01 ' || elapsed)) from
dur limit 1;
id elapsed typeof(0.0+strftime('%f', '1970-01-01 ' ||
elapsed))
---------- -----------
----------------------------------------------------
1 00:00:02.68 real
sqlite> select sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)) from dur;
sum(0.0+strftime('%f', '1970-01-01 ' || elapsed))
-------------------------------------------------
4038.85
sqlite> select time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed))) from
dur;
time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)))
-------------------------------------------------------
08:24:00
sqlite> select time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)),
'unixepoch') from dur;
time(sum(0.0+strftime('%f', '1970-01-01 ' || elapsed)), 'unixepoch')
--------------------------------------------------------------------
01:07:18
sqlite>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
