SQLite Forum

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'`)