Why aren't there date/time parsing built-in functions in SQLite

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Why aren't there date/time parsing built-in functions in SQLite

Dominique Devienne
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why aren't there date/time parsing built-in functions in SQLite

Keith Medcalf

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;

value        round((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 <[hidden email]> On Behalf
>Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 02:36
>To: General Discussion of SQLite Database <sqlite-
>[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why aren't there date/time parsing built-in functions in SQLite

Dominique Devienne
On Tue, Sep 10, 2019 at 1:05 PM Keith Medcalf <[hidden email]> 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 MMMMM:SS:FF (F for fractional seconds)
where MMMMM 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why aren't there date/time parsing built-in functions in SQLite

Igor Tandetnik-2
In reply to this post by Keith Medcalf
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why aren't there date/time parsing built-in functions in SQLite

Dominique Devienne
On Tue, Sep 10, 2019 at 4:35 PM Igor Tandetnik <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Why aren't there date/time parsing built-in functions in SQLite

Keith Medcalf
In reply to this post by Dominique Devienne

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 <[hidden email]> On
>Behalf Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 02:36
>To: General Discussion of SQLite Database <sqlite-
>[hidden email]>
>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
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users