Here is some proper code that to provide elapsedTime and timeModifier
functions. I think I have fixed all the weird corner cases, So I release this
code into the public domain to do with what you will. You need to wrap it in
the extension code or otherwise add it to amalgamation and provide a hook to
initialize it. Note -- other headers may be required.
/* We need the math header for the fabs and fmod functions used in _elapsedTime
function */
#include <math.h>
/*
** The elaspsedTime function will take either an input text string or an
** integer/floating point value.
**
** Input text in the d:h:m:s format will return a floating point value
** which is the total number of seconds represented. Each of d/h/m/s may
** be arbitrary floating point numbers.
**
** Note that the d:h:m:s result will be calculated based on the absolute
** value of each field and that the sign of the leftmost field determines
** the sign of the result.
**
** Input numbers (integer or float) are a total number of seconds will
** return the elapsed time string as d:hh:mm:ss.sss where d may be of
** arbitrary length, hh will be zero filled and between 0 and 23, mm will
** be zero filled and between 0 and 59. ss.sss will be between 0.000 and
** 59.999, zero filled, with exactly three decimal places.
**
** The timeModifier function will accept either an integer/floating point
** number of seconds or a text string as above. It returns a text string
** of the format "+s.sss seconds" or "-s.sss seconds" suitable for use
** as an argument to the builtin datetime functions.
*/
static void _elapsedTime(sqlite3_context *context, int argc, sqlite3_value
**argv) {
double maxspan = 464269060799.999;
switch (sqlite3_value_type(argv[0]))
{
case SQLITE_NULL:
{
return;
}
case SQLITE_TEXT:
{
double factors[4] = {86400.0, 3600.0, 60.0, 1.0};
double total = 0.0;
double sgn = 1.0;
char *start, *end;
/* Force conversion to utf-8 and make a copy of the text arg so we
can modify it */
sqlite3_value_text(argv[0]);
start = sqlite3_malloc(sqlite3_value_bytes(argv[0]) + 1);
strcpy(start, sqlite3_value_text(argv[0]));
end = start + strlen(start);
/* Compute totalseconds by parsing colon separated floats from the
right */
for (int j=3; j >= 0; j--)
{
double value;
char *i;
for (i=end; ((*i != ':') && (i >= start)); i--) ;
value = atof(i + 1);
total += fabs(value * factors[j]);
sgn = (value < 0) || (*(i + 1) == '-') ? -1.0 : 1.0;
if (i > start)
*i = 0;
else
break;
}
sqlite3_free(start);
if (total > maxspan)
return;
total *= sgn;
/* Compute and return output based on user context */
if ((intptr_t)sqlite3_user_data(context) == 0)
{
sqlite3_result_double(context, total);
}
else
{
char out[32];
sqlite3_snprintf(sizeof(out), out, "%+.3f seconds", total);
sqlite3_result_text(context, out, -1, SQLITE_TRANSIENT);
}
return;
}
default:
{
double s;
int d, h, m;
char out[32];
char *sgn;
/* Get our total seconds as a float */
s = sqlite3_value_double(argv[0]);
if (fabs(s) > maxspan)
return;
/* Return datetime modifier format */
if ((intptr_t)sqlite3_user_data(context) == 1)
{
sqlite3_snprintf(sizeof(out), out, "%+.3f seconds", s);
sqlite3_result_text(context, out, -1, SQLITE_TRANSIENT);
return;
}
/* Save our sign and use only absolute value */
sgn = s < 0 ? "-" : "";
s = fabs(s);
/* convert s to d/h/m/s */
d = (int)(s / 86400.0);
s = fmod(s, 86400.0);
h = (int)(s / 3600.0);
s = fmod(s, 3600.0);
m = (int)(s / 60.0);
s = fmod(s, 60.0);
sqlite3_snprintf(sizeof(out), out, "%s%d:%02d:%02d:%06.3f", sgn, d,
h, m, s);
sqlite3_result_text(context, out, -1, SQLITE_TRANSIENT);
return;
}
}
}
And the sqlite3_create_function calls to use:
sqlite3_create_function(db, "elapsedTime", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC, 0, _elapsedTime, 0, 0);
sqlite3_create_function(db, "timeModifier", 1,
SQLITE_UTF8|SQLITE_DETERMINISTIC,(void*)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 <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
