Re: [sqlite] FW: Why aren't there date/time parsing built-in functions in SQLite
Extension function (you need to add the wrappers and such) to convert a string
in 'd:h:m:s' into total seconds. d/h/m/s can all be arbitrary floating point
numbers. Omit from the left (that is, the rightmost number is seconds, the
next going left is minutes, then hours, then days. Crappy code without error
checking, but it works.
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, elapsedTime(value) from test;
valueelapsedTime(value)
--- --
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
static void _elapsed(sqlite3_context *context, int argc, sqlite3_value **argv) {
double parts[4] = {0.0};
double factors[4] = {86400.0, 3600.0, 60.0, 1.0};
double total = 0;
char *start;
char *end;
char *i;
int j;
start = sqlite3_value_text(argv[0]);
j = sqlite3_value_bytes(argv[0]);
start = sqlite3_malloc(j+1);
strcpy(start, sqlite3_value_text(argv[0]));
end = start + strlen(start);
for (j=3; j >= 0; j--)
{
for (i=end; ((*i != ':') && (i >= start)); i--) ;
parts[j] = atof(i+1);
if (i > start)
*i = 0;
else
break;
}
sqlite3_free(start);
for (j=0; j<4; j++)
total += (parts[j] * factors[j]);
sqlite3_result_double(context, total);
}
int sqlite3_sqltime_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi)
{
return sqlite3_create_function(db, "elapsedTime", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _elapsed,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 05:54
>To: SQLite mailing list
>Subject: Re: [sqlite] FW: Why aren't there date/time parsing built-in
>functions in SQLite
>
>On Tue, Sep 10, 2019 at 1:27 PM Keith Medcalf wrote:
>
>> 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. [...]
>
>
>In my quick reading of the doc [1], I didn't pickup any such mention. Is it
>even there?
>
>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.
>>
>
>See above. Not super-clear from the doc.
>
>As DRH mentioned recently about a different piece of doc, I suspect that
>doc hasn't been updated in years,
>and could use some attention IMHO. It's not specified what the various
>functions return in terms of types for
>example. It reads more like a terse user manual than reference
>documentation. Note sure how to make it more
>approachable exactly, but it seems hard to grasp exactly what's going on,
>at least to me. FWIW. --DD
>
>[1] https://www.sqlite.org/lang_datefunc.html
>___
>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] FW: Why aren't there date/time parsing built-in functions in SQLite
Dominique Devienne, on Tuesday, September 10, 2019 07:53 AM, wrote... > > As DRH mentioned recently about a different piece of doc, I suspect that > doc hasn't been updated in years, Is that a hint? :-) josé ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FW: Why aren't there date/time parsing built-in functions in SQLite
On Tue, Sep 10, 2019 at 1:27 PM Keith Medcalf wrote: > 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. [...] In my quick reading of the doc [1], I didn't pickup any such mention. Is it even there? 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. > See above. Not super-clear from the doc. As DRH mentioned recently about a different piece of doc, I suspect that doc hasn't been updated in years, and could use some attention IMHO. It's not specified what the various functions return in terms of types for example. It reads more like a terse user manual than reference documentation. Note sure how to make it more approachable exactly, but it seems hard to grasp exactly what's going on, at least to me. FWIW. --DD [1] https://www.sqlite.org/lang_datefunc.html ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FW: Why aren't there date/time parsing built-in functions in SQLite
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;
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
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
>Sent: Tuesday, 10 September, 2019 05:05
>To: 'SQLite mailing list'
>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;
>
>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
