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

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

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

Keith Medcalf

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;

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

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 <[hidden email]>
>Sent: Tuesday, 10 September, 2019 05:05
>To: 'SQLite mailing list' <[hidden email]>
>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;
>
>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: FW: Why aren't there date/time parsing built-in functions in SQLite

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

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

Jose Isaias Cabrera-4

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

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

Keith Medcalf
In reply to this post by Dominique Devienne

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 <[hidden email]> On Behalf
>Of Dominique Devienne
>Sent: Tuesday, 10 September, 2019 05:54
>To: SQLite mailing list <[hidden email]>
>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 <[hidden email]> 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
>[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