Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite
1, _elapsedTime,0, 0); -- 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 On >Behalf Of Dominique Devienne >Sent: Tuesday, 10 September, 2019 02:36 >To: General Discussion of SQLite Database 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
Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite
On Tue, Sep 10, 2019 at 4:35 PM Igor Tandetnik 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
Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite
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)
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why aren't there date/time parsing built-in functions in SQLite
On Tue, Sep 10, 2019 at 1:05 PM Keith Medcalf wrote:
> insert into test values ('00:00:07.86');
> select value,
>round((julianday(value) - julianday('00:00:00')) * 86400.0, 3)
> from test;
>
Thanks! As I guessed, I was indeed missing something.
But IMHO that something is definitely not obvious or straightforward though.
I still think a strptime()-like function to parse according to a format a
text date/time would be much more obvious.
With modifiers specifying the output units, no need to subtract and
multiply. But that's just me I guess.
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
>
Indeed, this works here, but if you have a duration with a number of hours
that exceeds 24h, or one measured in M:SS:FF (F for fractional seconds)
where M exceeds 60min, etc...
what do you do them? Back to extracting components via text manipulations
and doing your own math?
Seems to me one needs to be a super expert like you Keith to do these
things, when it ought to be simpler, no? --DD
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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;
valueround((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 On Behalf
>Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 02:36
>To: General Discussion of SQLite Database 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
[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
