SQLite Forum

Proposed new date/time function to return a unix timestamp
Login

Proposed new date/time function to return a unix timestamp

(1) By Richard Hipp (drh) on 2021-11-29 14:35:56 [link] [source]

I'd like to add a new date/time function that converts its argument into a unix timestamp (the number of seconds since 1970-01-01 00:00:00 UTC). So the result would be the same as "strftime('%s',ARGS...)" except that it would be an integer result rather than a string result.

Questions:

  1. What should the new function be called:

    • unixtime()
    • unixtimestamp()
    • secondssince1970()
    • Something else?

  2. Should the value returned always be an integer, or can it have a fractional part? So, for example, should the return value from "unixtime('2021-11-29 14:31:14.25')" be 1638196274 or 1638196274.25?

(2) By Stephan Beal (stephan) on 2021-11-29 14:46:26 in reply to 1 [link] [source]

unixtime()

...

should the return value from "unixtime('2021-11-29 14:31:14.25')" be 1638196274 or 1638196274.25?

Just spitballing...

It "might be interesting" (or might not) to instead follow JavaScript's lead and always use ms precision, but to do so without a fractional part. In JS the above would be:

  • 1638196274 ==> 1638196274000
  • 1638196274.25 ==> 1638196274250

in which case maybe call it jstime() instead of unixtime(), or additionally offer unixtimems() (or unixtime('...', 1)) to specify that it should return ms precision in whatever format is decided upon (fractional or integer).

(7) By anonymous on 2021-11-29 19:15:30 in reply to 2 [link] [source]

This could be very convenient when used in combination with the json1 functions, by simplifying both the SQL-side and the JS-side computations when passing timestamps.

(3) By Richard Hipp (drh) on 2021-11-29 15:31:31 in reply to 1 [link] [source]

Another name option:

  • unixepoch()

(4) By Richard Hipp (drh) on 2021-11-29 15:51:15 in reply to 3 [link] [source]

And after some experimentation, I think the new unixepoch() function should return an integer - discarding the milliseconds if any.

So for now, that's what I'm going with. But the decision is not set in stone so if you want to argue for an alternative, please do so.

(8) By Ryan Smith (cuz) on 2021-11-29 20:14:42 in reply to 4 [link] [source]

Firstly - this will be a great addition - thank you, I've been having to compute and translate timestamps forever, and now see the answer is here - but not quite....

Why blanket discard integer milliseconds? In most systems dealing with any sort of date-time accuracy (data logging especially), the milliseconds part is most important.

It is trivial to ignore integer milliseconds by integer-dividing by 1000. It's less trivial to get them if there is no single unixepoch function giving them, typically requiring daisy-chaining multiple functions together.
Maybe then a second function (as was suggested earlier) such as unixepoch_ms() or such?

Another post mentioned the added luxury this will afford when creating JS timestamps. Without milliseconds, this solution is only half a solution.

(9) By LukeL (LukeLuke) on 2021-11-30 20:55:56 in reply to 4 [link] [source]

I am working with IoT data on a daily basis and could make real use of extended functions for conversion betweeen unix epochs and datetime values, with sub-second precision, in both directions.

Currently, one may convert a datetime value into a unix epoch using strftime('%s', …), which is missing sub-second precision. Another way would be to convert to julianday() and to arithmetic on that, which is prone to floating-point precision limits resulting in flipping sub-second digits.

I suggest to add functions along the line of unix_epoch(), unix_epoch_ms(), and maybe even unix_epoch_us(), each converting a datetime value into a sufficiently large integer number with second / millisecond / microsecond precision, respectively.

Functions for unix epochs to datetime values with sub-second precision could also be helpful to avoid lengthy format strings.

Given these tools, I would proceed storing precision timestamps as integers in my databases and convert to datetime values when needed.

(10) By Arfrever Frehtes Taifersar Arahesis (Arfrever) on 2021-11-30 21:47:12 in reply to 9 [link] [source]

Modern time-related POSIX APIs [*] use struct timespec, which contains number of seconds and number of nanoseconds, so I recommend to use nanoseconds and not bother with milliseconds/microseconds.

[*] E.g. clock_getres(), clock_gettime(), clock_settime(), nanosleep(), clock_nanosleep(), thrd_sleep(), utimensat(), futimens()

(11) By Keith Medcalf (kmedcalf) on 2021-11-30 21:55:22 in reply to 9 [link] [source]

I suggest to add functions along the line of unix_epoch(), unix_epoch_ms(), and maybe even unix_epoch_us(), each converting a datetime value into a sufficiently large integer number with second / millisecond / microsecond precision, respectively.

The internal datetime structure maintains the datetime as a 64-bit integral count of milliseconds since the julian epoch. This necesarily entails that the maximum precision is 1 millisecond. Notwithstanding this limitation on precision, accuracy is also limited by the Operating System. Although the datetime returned on unix is precise to the nanosecond (and on Windows, to the hun -- hundreds of nanoseconds) the granularity of the sampling is controlled by the OS -- Windows only updates the system clock on a "tick", which is usually about 16 ms, although this can be changed.

Using a stamp value which is in the unit days since the julian epoch date, or in seconds since the unix epoch date, expressed as a double precision floating point number, is sufficiently accurate to express the timestamp as a number with millisecond precision with at least one guard digits over the entire range of ISO-8601 datetime text strings (0000-01-01T00:00:00.000Z to 9999-12-31T23:59:59.999Z) recognized by SQLite3.

sqlite> create table x(x);
sqlite> insert into x values ('0000-01-01 00:00:00.000');
sqlite> insert into x values ('1970-01-01 00:00:00.000');
sqlite> insert into x values ('2021-11-30 21:28:14.234');
sqlite> insert into x values ('9999-12-31 23:59:59.999');
sqlite> select x, julianday(x), epsilon(julianday(x))*86400, unixtime(x), epsilon(unixtime(x)) from x;
┌─────────────────────────┬──────────────────┬─────────────────────────────┬──────────────────┬──────────────────────┐
│            x            │   julianday(x)   │ epsilon(julianday(x))*86400 │   unixtime(x)    │ epsilon(unixtime(x)) │
├─────────────────────────┼──────────────────┼─────────────────────────────┼──────────────────┼──────────────────────┤
│ 0000-01-01 00:00:00.000 │ 1721059.5        │ 2.01165676116943e-05        │ -62167219200.0   │ 7.62939453125e-06    │
│ 1970-01-01 00:00:00.000 │ 2440587.5        │ 4.02331352233887e-05        │ 0.0              │ 1.11022302462516e-16 │
│ 2021-11-30 21:28:14.234 │ 2459549.39460919 │ 4.02331352233887e-05        │ 1638307694.234   │ 2.38418579101563e-07 │
│ 9999-12-31 23:59:59.999 │ 5373484.49999999 │ 8.04662704467773e-05        │ 253402300799.999 │ 3.0517578125e-05     │
└─────────────────────────┴──────────────────┴─────────────────────────────┴──────────────────┴──────────────────────┘

NB: UnixTime code previously posted and differs from the new unixepoch function in that it returns a double precision floating point value which includes milliseconds.

which is prone to floating-point precision limits resulting in flipping sub-second digits.

This is what is referred to as an old wives tale. It is promulgated by the same persons who barf up the same demonstration of ignorance when dealing with storing money in floating point. As you can see from the above table, a double does not contain enough precision to express the value reliably to more than millisecond precision.

(12) By LukeL (LukeLuke) on 2021-12-04 15:14:58 in reply to 11 [link] [source]

The internal datetime structure maintains the datetime as a 64-bit integral count of milliseconds since the julian epoch. This necesarily entails that the maximum precision is 1 millisecond.

Thank you for this internal detail, I agree it makes anything more than millisecond precision wasted efford when converting to/from datetime.

Notwithstanding this limitation on precision, accuracy is also limited by the Operating System.

A datetime value does not necessarily have to be generated by the operating system of the box running sqlite, or any operating system at all. Waiting long enough, someone with a high-precision clock in an embedded system will try to store data in sqlite. Still, it gives an indication which precision is sufficient for typical users, and the milliecond precision of datetime applies anyway.

Using a stamp value which is in the unit days since the julian epoch date, or in seconds since the unix epoch date, expressed as a double precision floating point number, is sufficiently accurate to express the timestamp as a number with millisecond precision with at least one guard digits over the entire range of ISO-8601 datetime text strings (0000-01-01T00:00:00.000Z to 9999-12-31T23:59:59.999Z) recognized by SQLite3.

Thank you for checking this. I expect that users (or at least, I) will use an upcoming conversion function for equality comparison in SELECT and JOIN conditions (e.g. unixtime(some_datetime) = some_unix_timestamp). Wouldn't an integer (millisecond) return value be much more robust?

To sum up: can you please give access to the internal 64-bit integral count of milliseconds, shifted to start at the unix epoch, via an SQL-accessible function returning (signed) integral milliseconds?

(13) By Keith Medcalf (kmedcalf) on 2021-12-05 02:10:47 in reply to 12 [link] [source]

As long as you account "estimates being nearly equal" there will be no problems.

Storing the timestamp as a scaled integer does not really help as you still have to solve the same problem -- that is, two values which appear identical may originate from a source that is incorrect, and two values which appear to differ be a millisecond may in fact only differ by a picosecond.

(5.1) By Keith Medcalf (kmedcalf) on 2021-11-29 17:20:29 edited from 5.0 in reply to 1 [source]

I simply use this function in the core (cannot be a UDF since it uses internal data structures):

/*
** UnixTime function returns the floating point unix epoch time.
** It uses the iJD maintained by the VDBE and is therefore only
** precise to a millisecond.
*/

static void _UnixTime(sqlite3_context *context, int argc, sqlite3_value **argv) {
    DateTime x;
    if (isDate(context, argc, argv, &x) == 0) {
        sqlite3_result_double(context, ((double)(x.iJD - 210866760000000ll)) / 1000.0);
    }
}

(6) By Richard Hipp (drh) on 2021-11-29 18:56:26 in reply to 5.1 [link] [source]

The actual implementation on trunk is similar: https://sqlite.org/src/artifact/d0f09f7924a27e0d?ln=968-984

Preliminary documentation of the new enhancements can be seen at https://sqlite.org/draft/lang_datefunc.html.