SQLite Forum

Calculating duration in ISO8601 timestamp
Login

Calculating duration in ISO8601 timestamp

(1) By anonymous on 2021-07-20 20:45:10 [link] [source]

Hi,

Assume this is the start time of an event: 2021-07-19 08:43:46:956

and this is the end time: 2021-07-20 10:26:41:357

The times are using 24 hour timestamps.

Can the duration be calculated between these?

What I've tried:

select
	min(datetime) as Start,
	max(datetime) as End,
	datetime(max(datetime)) - datetime(min(datetime)) as duration	
from
	table1

...

select
	strftime(max(datetime)) as max,
	strftime(min(datetime)) as max,
strftime(max('%Y-%m-%d %H:%M:%S',datetime)) - strftime(min('%Y-%m-%d %H:%M:%S',datetime)) as Duration

from table1

...

select
	start,
	end,
	duration
from (
	select
		min(datetime) as start,
		max(datetime) as end,
		julianday('datetime') - julianday('now') as duration
from table1
);

Results are always blank or 0. Clearly it can't be! The columns are of 'text'. Do I need to cast it to something else?

Thanks!

(2) By Warren Young (wyoung) on 2021-07-20 21:00:49 in reply to 1 [link] [source]

julianday('datetime')

This is the right track, but lose the quotes. You've passed the literal string "datetime" to the function, not the name of a column.

If you had to quote the column name, you'd use double-quotes or backquotes. See the quoting rules for more details.

(3) By anonymous on 2021-07-20 21:09:39 in reply to 2 [link] [source]

Ah, that was probably a copy/paste mistake from me trying different things.

select
	julianday(min(datetime))
from table1

This returns nothing/blank.

(4) By Harald Hanche-Olsen (hanche) on 2021-07-20 21:10:44 in reply to 1 [link] [source]

Your input format is wrong: You need a decimal point, not a colon, between the seconds and the fractional seconds.

▶▶▶ create table foo(datetime text);
▶▶▶ insert into foo values ('2021-07-19 08:43:46.956'),('2021-07-20 10:26:41.357');
▶▶▶ select julianday(max(datetime))-julianday(min(datetime)) from foo;
julianday(max(datetime))-julianday(min(datetime))
-------------------------------------------------
1.07146297441795

(5) By anonymous on 2021-07-20 21:38:45 in reply to 4 [source]

Ah! Good tip.

Until the time is using a decimal, does that mean the julianday function and friends won't know how to read the datetime as its stored?

select
	min(strftime('%Y-%m-%d %H:%M:%S.%f',datetime))  as start
From
	table1

Doesn't give me anything back.

Neither does dropping the .%f:
min(strftime('%Y-%m-%d %H:%M:%S',datetime)) as start

(7.1) By Warren Young (wyoung) on 2021-07-20 22:08:44 edited from 7.0 in reply to 5 [link] [source]

If you use a colon instead of a dot to separate the whole seconds from the fractional seconds, it isn't ISO 8601, so it isn't legal in SQLite. This includes strftime().

Rather than continue to arm-twist bad data formats in place, I'd reimport those columns. While you're at it, add a "T" between the date and time parts and put a "Z" on the end to make it absolutely clear that you're giving the time in UTC.

And if you aren't using UTC, convert the time from whatever local time zone you mean to UTC so the data doesn't break again when you encounter two or more time zones in a single calculation.

(11) By anonymous on 2021-07-21 06:05:03 in reply to 7.1 [link] [source]

While you're at it, add a "T" between the date and time parts and put a "Z" on the end to make it absolutely clear that you're giving the time in UTC.

Do you mean like this?

2021-07-19T08:43:46.956+0800

(14) By Adrian Ho (lexfiend) on 2021-07-21 11:25:54 in reply to 11 [link] [source]

Do you mean like this?

2021-07-19T08:43:46.956+0800

You're missing a colon:

$ sqlite3
sqlite> select julianday('2021-07-19T08:43:46.956+0800');

sqlite> select julianday('2021-07-19T08:43:46.956+08:00');
2459414.53040458

The timezone format is specified in https://sqlite.org/lang_datefunc.html#time_values.

(9) By Keith Medcalf (kmedcalf) on 2021-07-20 22:34:25 in reply to 5 [link] [source]

%S means seconds as two digits zero-filled as in 00 to 59
%f means seconds as six digits zero-filled as in 00.000 to 59.999

The formatting string '%Y-%m-%d %H:%M:%S.%f' which would produce output like:
2021-07-20 22:31:19.19.275

The correct formatting string is '%Y-%m-%d %H:%M:%f' which would produce output like:
2021-07-20 22:31:19.275

(6) By Keith Medcalf (kmedcalf) on 2021-07-20 21:55:51 in reply to 1 [link] [source]

Here are some snippets of an UDF that does various calculations with elapsedTime values. An alternate calling format can also provide the output in a format that can be used directly as a modifier by the strftime/datetime functions.

/*
** 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 (which
** must be in the range -1e21 to 1e21) and 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 00.000 and 59.999, zero filled, with
** exactly three decimal places.
*/

static void _elapsedTime(sqlite3_context *context, int argc, sqlite3_value **argv) {

    const double maxspan = 464269060799.999;

    switch (sqlite3_value_type(argv[0]))
    {
        case SQLITE_NULL:
        {
            return;
        }

        case SQLITE_BLOB:
        {
            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;

            if ((intptr_t)sqlite3_user_data(context) == 0)
            {
                sqlite3_result_double(context, total);
            }
            else
            {
                char out[32];
                /* Output datetime modifier format */
                sqlite3_snprintf(sizeof(out), out, "%+.3f seconds", total);
                sqlite3_result_text(context, out, -1, SQLITE_TRANSIENT);
            }
            return;
        }

        default: /* numeric */
        {
            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;
        }
    }
}

With the following registration functions:

    sqlite3_create_function(db, "elapsedTime",  1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)0, _elapsedTime, 0, 0);
    sqlite3_create_function(db, "timeModifier", 1, SQLITE_UTF8|SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS, (void*)1, _elapsedTime, 0, 0);

Note that the input is either the specified elapsedTime string or an integer/float duration. You could, for example, calculate the number of seconds between "a" and "b" as "((julianday(b) - julianday(a))*86400" so that elapsedTime((julianday(b)-julianday(a))*86400) will return the d:hh:mm:ss.sss string representing the difference.

Similarly timeModifier will return this in a format that can be used by the datetime family of functions such that "datetime(a, timeModifier((julianday(b)-julianday(a))*86400))" returns the datetime string b ...

The boundary 464269060799.999 represents the maximum number of seconds that the datetime functions can deal (the date '9999-12-31 23:59:59.999')

(8) By Keith Medcalf (kmedcalf) on 2021-07-20 22:27:12 in reply to 6 [link] [source]

See http://www.dessus.com/files/sqlite3extensions.zip for all the code and patches.

(12) By MBL (RoboManni) on 2021-07-21 06:20:55 in reply to 8 [link] [source]

thanks for this link ... seems also something for me to go through...

(10) By anonymous on 2021-07-20 23:20:43 in reply to 1 [link] [source]

All fixed! The app generating the data now uses a decimal instead of a colon!

select
	start,
	end,
	duration
from (
	select
		min(datetime) as start,
		max(datetime) as end,
		julianday(max(`datetime`)) - julianday(min(`datetime`)) as duration
from table1
);
┌─────────────────────────┬─────────────────────────┬─────────────────┐
│          start          │           end           │    duration     │
├─────────────────────────┼─────────────────────────┼─────────────────┤
│ 2021-07-19 08:43:46.956 │ 2021-07-20 15:18:26.623 │ 1.2740702200681 │
└─────────────────────────┴─────────────────────────┴─────────────────┘

Thanks for the time and education!

(13) By MBL (RoboManni) on 2021-07-21 06:28:00 in reply to 10 [link] [source]

SQLite version 3.36.0 2021-06-18 18:36:39

sqlite> select julianday('2021-07-20 15:18:26.623');
2459416.13780814
sqlite> select julianday(substr('2021-07-20 15:18:26:623',1,19)||'.'||substr('2021-07-20 15:18:26:623',21,23));
2459416.13780814
sqlite> select julianday(substr(dt,1,19)||'.'||substr(dt,21,23)) from (select '2021-07-20 15:18:26:623' as dt);
2459416.13780814
sqlite>