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;
value elapsedTime(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 <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf
>Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 05:54
>To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>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 <kmedc...@dessus.com> 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
