Inspired by this blog post about millisecond Unix timestamps, I have found working with Unix timestamps with millisecond precision in SQLite to be slightly frustrating. Technically you are able to do so with builtin functions, but requires manual work and confusing constants.

For example, the Date and Time Functions documentation offers this recipe:

Compute the time since the unix epoch in seconds with millisecond precision:

SELECT (julianday('now') - 2440587.5)*86400.0;
1681017839.70301

Again, this works, but a few problems come up. For one, the 2440587.5 and 86400.0 are "magic", easy to make typos with, and are hard to explain in code reviews.

Additionally, the number is returned is a bit hard to work with. The millisecond values are awkward decimal values, and in the above example, I'm not even sure if the .01 values in .70301 are reliable. Now this could be solved with multiplying this by 1000 and casting as an integer, like so:

SELECT cast((julianday('now') - 2440587.5)*86400.0 * 1000 as integer);
1681017792608

But that just adds more complex constants and syntax to the query.

Proposal: A new unixepoch_ms() SQL Scalar Function

I propose a new unixepoch_ms() function to be added to SQLite, similar to the unixepoch(). However, instead of returning the number of seconds) since the unix epoch of 1970-01-01 00:00:00 GMT, it returns the number of milliseconds.

Some example of how this would work:

sqlite> select unixepoch_ms();
1681019701793
sqlite> select unixepoch_ms();
1681019702154
sqlite> select unixepoch_ms();
1681019702564

It take the same arguments and follow the same convention as unixepoch() and friends, but returns the number of milliseconds since Unix epoch. It would also be great to see as a new modifier similar to "unixepoch", but I'm unsure how easy that will be.

A few benefits I see of using unixepoch_ms() over unixepoch():

1. In my tables where I have updated_at or deleted_at fields, I typically want millisecond precision in those stored timestamps. Second precision usually works, but can make things easier to debug in write-heavy tables
2. Millisecond epoch times are easier to integrate with Javascript, which use millisecond values for Date() objects

I'd definitely appreciate this. I spent a while figuring out the pattern I wrote about in my TIL, mainly because I had trouble convincing myself it was the best way of solving this problem - if felt far too complicated!

yes, it would be a nice convenience to have unixepoch_ms(). As showed in another thread, I use strftime('%s', '<yyyy-mm-dd>') * 1000 to get my milliseconds from SQLite. But, not having that doesn't seem to be a deal-breaker to me. This is syntactic sugar that can be easily added in our programming environment.

That * 1000 trick doesn't work for me, because the reason I want milliseconds is that I want to have millisecond resolution for things like row creation time. If I take seconds * 1000 I may as well just record the seconds directly - I'm not gaining any extra detail.

Use julianday(). THe returned juliandate is accurate to 1 ms across the entire range of datetime display values.

That's what I'm using. It's so weird and unintuitive though! I honestly had trouble believing it was the "right" way to do this.

comparing the julianday() and the strftime…mod(,1) approaches

sqlite> select ms1, ms2, ms1-ms2 as diff from (select 1000*(strftime('%s','now')+mod(strftime('%f','now'),1)) as ms1, (julianday() - 2440587.5) * 86400 * 1000 as ms2);
ms1              ms2               diff
---------------  ----------------  ------------
1681070615517.0  1681070615517.01  -0.009765625

The mod() function doesn't appear to be available by default in some SQLite installations:

https://latest.datasette.io/_memory?sql=select+ms1%2C+ms2%2C+ms1-ms2+as+diff+from+%28select+1000*%28strftime%28%27%25s%27%2C%27now%27%29%2Bmod%28strftime%28%27%25f%27%2C%27now%27%29%2C1%29%29+as+ms1%2C+%28julianday%28%29+-+2440587.5%29+*+86400+*+1000+as+ms2%29%3B

This seems to work though:

select
  (1000 * (strftime('%s', 'now'))) + cast(
    substr(
      strftime('%f', 'now'),
      instr(strftime('%f', 'now'), '.') + 1
    ) as integer
  ) as timestamp_ms

Comparison: https://latest.datasette.io/_memory?sql=select+%27fractional%27+as+label%2C%0D%0A++%281000+*+%28strftime%28%27%25s%27%2C+%27now%27%29%29%29+%2B+cast%28%0D%0A++++substr%28%0D%0A++++++strftime%28%27%25f%27%2C+%27now%27%29%2C%0D%0A++++++instr%28strftime%28%27%25f%27%2C+%27now%27%29%2C+%27.%27%29+%2B+1%0D%0A++++%29+as+integer%0D%0A++%29+as+timestamp_ms%0D%0Aunion%0D%0Aselect+%27julian%27%2C+%28julianday%28%29+-+2440587.5%29+*+86400+*+1000%0D%0Aunion%0D%0Aselect+%27julian_as_int%27%2C+cast%28%28julianday%28%29+-+2440587.5%29+*+86400+*+1000+as+integer%29

Here is another one:

select cast( strftime('%s','now')||substr(strftime('%f','now'),-3) as integer ) as unixepoch_ms;

which "ticks these boxes":

• only core functions like SUBSTR, ||, STRFTIME and CAST used
• no floating point calculations other then the ones internal to STRFTIME(...)
• generates integer "milliseconds epoch" (or string if CAST left out)

Relies on having exactly 3 digits after decimal point on the %f fractional seconds: SS.SSS modifier. Which is the case since a least fall 2006 or spring 2007 (using %02.3f or %06.3f for the printf there). And probably before that in the initial date.c from 2003 (using %02d.%03d).

Some round trip check on datasette, thanks for the cool tool Simon!

This query seems to obtain the current time from the OS twice. According to the documentation the now modifier used twice to obtain ms1 should be all right, the current time is obtained only once. But this seems not to apply to the julianday() call for ms2, where the current time is obtained a second time. This would explain the small difference of a few μsec?

Appearances can be deceiving. The "current time" is accessed from the Operating System the first time it is used in each step and is thereafter a slochange constant.

A Julian Millisecond is 1.0/86400000.0 of a julian day, or 1.1574074074074074e-08 julian days.

You "discombooberated" a fractional remainder when converting to julianday units, so you have to "de-discombooberate" your fraction when converting from julianday units to seconds.

In simple terms this means:

unixepoch <= roundhe((julianday() - 244587.5) / 86400.0,3)

where roundhe(x,3) means to round x half-even to 3 decimal places.

The extraneous digits are called "guard digits" and there are between 1.5 and 4.5 of them over the entire range of valid julianday values.

Like all arithmetic the point is to handle the point. If you cannot do that, then there is no point and you should report to the unemployable office.

(alternatively, see what happens when there has been no experience using computation devices that require application of thought and expertise such as the slide rule but instead has been rote-a-rized (committed to memory) without understanding)

I tested this a while ago and confirmed that "now" is stable within a single query: https://til.simonwillison.net/sqlite/now-argument-stability

Your test method was defective. 'now' is step stable. By default it is reset to 0 each time the VM row-generator is entered. It is, however, a slochange constant, so unless you request the re-evaluation of the function, then you will get the cached slochange value.

For example, if you re-execute the julianday function for each step, you will get a different answer for each step:

select julianday('now', (value-value) || ' seconds')
  from generate_series(1,1000000);

contrast this with the following code that only executes the julianday function once and thus makes it appear to the uneducated observer that 'now' is statement stable:

select julianday('now')
  from generate_series(1,1000000);

You could modify the code to make 'now' statement stable (ie, only reset 'now' when an VM executes statement 0 rather than every time is executes). By default as designed, however, it is only step-stable.

You may want to check Keith Medcalf's time/date extensions at http://www.dessus.com/files/sqlite3extensions.zip

The problem with solving this with extensions is that it's pretty common for available SQLite environments to disable extensions entirely.

I've run into this on macOS - the SQLite CLI tool that ships with the system disables extensions, and on Ubuntu Linux. It's also common for Python installations to have SQLite extension loading disabled.

I prefer a database engine to just stick to database stuff. Anything more than that is handled in the language I am using to implement the application on top of the database—for me currently with SQLite, that is usually Python. That offers a whole range of functions for doing date/time stuff and other useful things in its standard library, so I don’t feel the need for the SQL implementation to duplicate this as well.

I'm mostly on board with this, but in the case of "give me the current Unix timestamp in milliseconds" I have very good reasons for wanting that to be solved within the database itself.

I'm designing a system that uses triggers to record when specific inserts/updates happened - which is exactly what triggers are meant to be used for. But I'm having to use confusing idioms to get SQLite to record that timestamp with millisecond precision.

Here are the triggers I'm currently using: https://github.com/simonw/sqlite-history/blob/0.1/README.md#how-this-works

You might find an extension I wrote to be useful. The resolution is much finer than milliseconds, so you might have to do some rounding.

Sadly I can't solve this problem with extensions, because I'm writing software for other people to run and I've found that a surprising number of existing SQLite / SQLite-Python installations have extension support disabled. It's really frustrating!

Normally I'd agree, but there's already precedence in SQLite for adding date/time helpers. You could say the same thing about the unixepoch()/julianday()/datetime()/date()/time() functions that already exists: technically, you could already replicate it yourself using only strftime(). But the SQLite team found a good reason to include those functions in the standard library, and I think it's reasonable to include unixepoch_ms() in with them.

Plus, it wouldn't drastically change SQLite: using unixepoch() as an example, it appears to only be a few lines of code, so it wouldn't be a large change like other SQL functions would be.

Additionally, I think you technically aren't able to do this with application-defined functions: The "now" argument is step stable only in the builtin date and time functions, so if you were to create an application-defined unixepoch_ms() function, it would not behave the same as unixepoch() or strftime().

I have created a patch for this request. I have signed and mailed the copyright release to the address listed at this page.

The attached patch is solely my work. The only reference I used was the SQLite codebase itself. I did not consult or copy-paste any code from anywhere else for this patch. I have signed the copyright release, and am willing to sign or do anything else to get this patch in.

I am posting this patch request to the original SQLite forum post, and as an email to drh.

Changes

This patch adds a single new unixepoch_ms() SQL scalar function to SQLite. It works in a similar way as the unixepoch() function, taking the same arguments, but instead returns the number of milliseconds since 1970-01-01 00:00:00 UTC, rather than just the number of seconds.

Some usage examples:

sqlite> select unixepoch_ms();
1681713630292

sqlite> select unixepoch_ms('2022-01-01');
1640995200000

sqlite> select unixepoch_ms('1970-01-01 00:00:00.999');
999

This patch also adds a new ”unixepoch_ms" datetime modifier, inside parseModifier() in src/date.c. This allows other datetime functions like datetime() or strftime() to use the ”unixepoch_ms” modifier.

sqlite> select datetime(2459607050, 'unixepoch_ms');
1970-01-29 11:13:27

sqlite> select strftime('%Y-%m-%d %H:%M:%f', 1, 'unixepoch_ms');
1970-01-01 00:00:00.001

Motivation

First, the actual “milliseconds since unix epoch” number is an increasingly used datetime format for various programming language. Both JavaScript and Java use this value as the constructors for their Date objects, and most other programming languages also accept “milliseconds since unix epoch” as an argument. The JavaScript example is the most important, especially since now many SQLite developers are accessing their SQLite data from client-side JavaScript, either using a proxy server or SQLite’s builtin WASM support.

Additionally, the current tools for millisecond-precision in builtin datetime functions in SQLite are a bit lacking. You have two options: using julianday() as a float, or using strftime() with the custom format ”%Y-%m-%d %H:%M:%f” as a string. These both work, but have a few usability issues:

• Julian days work great for operations like “how many days were between these two dates”, but are much more difficult for millisecond-precision operations. For example, to answer “how many milliseconds were between created_at and updated_at, you end up with select cast( (updated_at - created_at) * 86400000.0 as integer) from t, which is pretty syntactically heavy.
• For strftime(), you often have to repeat the ”%Y-%m-%d %H:%M:%f” format string over and over again in many different parts of your code, which can be tedious.

As an example, here’s a table where the datetime column is stored as julian days:

┌──────────────────┐
│    created_at    │
├──────────────────┤
│ 2460053.24177803 │
│ 2460053.24181899 │
└──────────────────┘

And now as milliseconds since unixepoch:

┌───────────────┐
│  created_at   │
├───────────────┤
│ 1681840089622 │
│ 1681840093161 │
└───────────────┘

If you wanted to see “how far apart were these two events”, it’s hard to tell at first glance with the Julian day table. You can easily tell they happened on the same day, but everything in the decimal places can’t be approximated easily. Meanwhile with the millisecond unix one, you can easily tell that there was around 3-4 seconds between these events by scanning the last few digits.

Finally, using “milliseconds since unix epoch” for columns has some storage benefits. The result from unixepoch_ms() is stored as an integer, which take up 0-8 bytes on-disk. Compared to Julian day real numbers, which are always 8 bytes, and '%Y-%m-%d %H:%M:%f', which is always a string with 23 bytes.

Here’s a test for these three different datetime storage strategies, on a single-column table with 1 million rows:

create table iso_storage as 
  select 
    strftime('%Y-%m-%d %H:%M:%f') as created_at
  from generate_series(1, 1e6);

create table julian_storage as 
  select 
    julianday() as created_at
  from generate_series(1, 1e6);

create table unix_storage as 
  select 
    unixepoch_ms() as created_at
  from generate_series(1, 1e6);

select name, sum(pgsize) 
from dbstat
where name not in ('sqlite_schema') 
group by 1 
order by 2;

┌────────────────┬─────────────┐
│      name      │ sum(pgsize) │
├────────────────┼─────────────┤
│ unix_storage   │ 14053376    │
│ julian_storage │ 16093184    │
│ iso_storage    │ 31330304    │
└────────────────┴─────────────┘

As you can see, storing it as “milliseconds since unixepoch” takes up less than half the space as the ISO8601 text strategy, and ~12% smaller than the Julian option. I know a lot of different variables effect how large a SQLite table is on-disk, but having a builtin millisecond-level precision timestamp that can be stored as an integer can be extremely useful for many people.

Cost

The technical cost of this patch is extremely low, only a few new lines of C, essentially emulating the unixepoch() function and ’unixepoch' modifier. The new unixepoch_ms() addition shouldn’t have any measurable performance regression for pre-existing SQLite installations, since it’s a completely new function. The new ”unixepoch_ms" modifier does add a new if/else if branch, but I’d imagine that’s negligible.

Compiling the sqlite3 CLI on my Mac with make sqlite3 before this patch was 1,952,408 bytes (1.95 MB). After this patch, it is 1,952,528 bytes, an increase in 120 bytes, a 0.0061% increase. To be fair, the sqlite3 CLI is much bigger than the core library, but the general point stands.

Considerations

Technically, you can emulate unixepoch_ms() with an expression like the following:

SELECT cast((julianday('now') - 2440587.5)*86400000.0 as integer);

However, there are a few ergonomic issues with this:

• Typing out those “magic constants” like 2440587.5 and 86400000.0 can become tedious.
• In my experience, it’s difficult to explain those magic constants and they constantly come up in code reviews.
• Moving this logic into a new function also removes a few VM instructions from the alternative:

sqlite> explain SELECT cast((julianday('now') - 2440587.5)*86400000.0 as integer);;
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     10    0                    0   Start at 10
1     Once           0     6     0                    0   
2     String8        0     4     0     now            0   r[4]='now'
3     Function       1     4     3     julianday(-1)  0   r[3]=func(r[4])
4     Real           0     5     0     2440587.5      0   r[5]=2440587.5
5     Subtract       5     3     2                    0   r[2]=r[3]-r[5]
6     Multiply       6     2     1                    0   r[1]=r[6]*r[2]
7     Cast           1     68    0                    0   affinity(r[1])
8     ResultRow      1     1     0                    0   output=r[1]
9     Halt           0     0     0                    0   
10    Real           0     6     0     86400000       0   r[6]=86400000
11    Goto           0     1     0                    0   
sqlite> explain select unixepoch_ms();
addr  opcode         p1    p2    p3    p4             p5  comment      
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     6     0                    0   Start at 6
1     Once           0     3     0                    0   
2     Function       0     0     2     unixepoch_ms(-1)  0   r[2]=func()
3     Copy           2     1     0                    0   r[1]=r[2]
4     ResultRow      1     1     0                    0   output=r[1]
5     Halt           0     0     0                    0   
6     Goto           0     1     0                    0   
sqlite> 

Which is extremely helpful in write-heavy INSERT or CREATE TABLE commands.

Additionally, one might say that you can emulate unixepoch_ms() as an application-defined function or in a loadable SQLite extension. While mostly correct, it’s not entirely true. These builtin datetime functions are step-stable, meaning multiple invocation of these functions that reference 'now' return the same value inside the same sqlite3_step() call. This is only possible with the internal sqlite3StmtCurrentTime() function, which is not exposed to either application-defined functions or loadable extensions.

Also, it’s not possible to add any new modifiers like 'unixepoch_ms' to the builtin datetime functions.

Patch

The following patch can be applied to the latest trunk version of SQLite (not the amalgamation). Only src/date.c and test/date3.test are modified.

Index: src/date.c
==================================================================
--- src/date.c
+++ src/date.c
@@ -724,17 +724,34 @@
       }
       break;
     }
 #endif
     case 'u': {
+      /*
+      **    unixepoch_ms
+      **
+      ** Treat the current value of p->s as the number of  
+      ** milliseconds since 1970.  Convert to a real julian day number.
+      */
+      if( sqlite3_stricmp(z, "unixepoch_ms")==0 && p->rawS ){
+        if( idx>1 ) return 1; 
+        r = p->s + 210866760000000.0;
+        if( r>=0.0 && r<464269060800000.0 ){
+          clearYMD_HMS_TZ(p);
+          p->iJD = (sqlite3_int64)(r + 0.5);
+          p->validJD = 1;
+          p->rawS = 0;
+          rc = 0;
+        }
+      }
       /*
       **    unixepoch
       **
       ** Treat the current value of p->s as the number of
       ** seconds since 1970.  Convert to a real julian day number.
       */
-      if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
+      else if( sqlite3_stricmp(z, "unixepoch")==0 && p->rawS ){
         if( idx>1 ) return 1;  /* IMP: R-49255-55373 */
         r = p->s*1000.0 + 210866760000000.0;
         if( r>=0.0 && r<464269060800000.0 ){
           clearYMD_HMS_TZ(p);
           p->iJD = (sqlite3_int64)(r + 0.5);
@@ -1005,10 +1022,27 @@
   if( isDate(context, argc, argv, &x)==0 ){
     computeJD(&x);
     sqlite3_result_int64(context, x.iJD/1000 - 21086676*(i64)10000);
   }
 }
+/*
+**    unixepoch_ms( TIMESTRING, MOD, MOD, ...)
+**
+** Return the number of milliseconds since the 
+** unix epoch of 1970-01-01 00:00:00 GMT.
+*/
+static void unixepochMsFunc(
+  sqlite3_context *context,
+  int argc,
+  sqlite3_value **argv
+){
+  DateTime x;
+  if( isDate(context, argc, argv, &x)==0 ){
+    computeJD(&x);
+    sqlite3_result_int64(context, x.iJD - 21086676*(i64)10000000);
+  }
+}
 
 /*
 **    datetime( TIMESTRING, MOD, MOD, ...)
 **
 ** Return YYYY-MM-DD HH:MM:SS
@@ -1336,22 +1370,23 @@
 ** external linkage.
 */
 void sqlite3RegisterDateTimeFunctions(void){
   static FuncDef aDateTimeFuncs[] = {
 #ifndef SQLITE_OMIT_DATETIME_FUNCS
-    PURE_DATE(julianday,        -1, 0, 0, juliandayFunc ),
-    PURE_DATE(unixepoch,        -1, 0, 0, unixepochFunc ),
-    PURE_DATE(date,             -1, 0, 0, dateFunc      ),
-    PURE_DATE(time,             -1, 0, 0, timeFunc      ),
-    PURE_DATE(datetime,         -1, 0, 0, datetimeFunc  ),
-    PURE_DATE(strftime,         -1, 0, 0, strftimeFunc  ),
-    DFUNCTION(current_time,      0, 0, 0, ctimeFunc     ),
-    DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc),
-    DFUNCTION(current_date,      0, 0, 0, cdateFunc     ),
+    PURE_DATE(julianday,        -1, 0, 0, juliandayFunc   ),
+    PURE_DATE(unixepoch,        -1, 0, 0, unixepochFunc   ),
+    PURE_DATE(unixepoch_ms,     -1, 0, 0, unixepochMsFunc ),
+    PURE_DATE(date,             -1, 0, 0, dateFunc        ),
+    PURE_DATE(time,             -1, 0, 0, timeFunc        ),
+    PURE_DATE(datetime,         -1, 0, 0, datetimeFunc    ),
+    PURE_DATE(strftime,         -1, 0, 0, strftimeFunc    ),
+    DFUNCTION(current_time,      0, 0, 0, ctimeFunc       ),
+    DFUNCTION(current_timestamp, 0, 0, 0, ctimestampFunc  ),
+    DFUNCTION(current_date,      0, 0, 0, cdateFunc       ),
 #else
     STR_FUNCTION(current_time,      0, "%H:%M:%S",          0, currentTimeFunc),
     STR_FUNCTION(current_date,      0, "%Y-%m-%d",          0, currentTimeFunc),
     STR_FUNCTION(current_timestamp, 0, "%Y-%m-%d %H:%M:%S", 0, currentTimeFunc),
 #endif
   };
   sqlite3InsertBuiltinFuncs(aDateTimeFuncs, ArraySize(aDateTimeFuncs));
 }

Index: test/date3.test
==================================================================
--- test/date3.test
+++ test/date3.test
@@ -143,7 +143,38 @@
   WITH inc(x) AS (VALUES(-10) UNION ALL SELECT x+1 FROM inc WHERE x<100)
   SELECT count(*) FROM inc
   WHERE datetime('1970-01-01',format('%+d days',x))
      <> datetime(unixepoch('1970-01-01',format('%+d days',x)),'auto');
 } {63}
+
+# unixepoch_ms(time-value, modifier, modifier, ...)
+datetest 6.1  {unixepoch_ms('1970-01-01')}              {0}
+datetest 6.3  {unixepoch_ms('1970-01-01 00:00:00.001')} {1}
+datetest 6.4  {unixepoch_ms('1970-01-01 00:00:00.999')} {999}
+datetest 6.5  {unixepoch_ms('1970-01-01 00:00:01.000')} {1000}
+datetest 6.2  {unixepoch_ms('1970-01-01 00:00:01')}     {1000}
+datetest 6.6  {unixepoch_ms('1969-12-31 23:59:59')}     {-1000}
+datetest 6.7  {unixepoch_ms('2106-02-07 06:28:15')}     {4294967295000}
+datetest 6.8  {unixepoch_ms('2106-02-07 06:28:16')}     {4294967296000}
+datetest 6.9  {unixepoch_ms('9999-12-31 23:59:59')}     {253402300799000}
+datetest 6.9  {unixepoch_ms('9999-12-31 23:59:59.999')} {253402300799999}
+datetest 6.10 {unixepoch_ms('0000-01-01 00:00:00')}     {-62167219200000}
+
+datetest 6.11 {strftime('%Y-%m-%d %H:%M:%f', -62167219200000,   'unixepoch_ms')} {0000-01-01 00:00:00.000}
+datetest 6.12 {strftime('%Y-%m-%d %H:%M:%f', -1,                'unixepoch_ms')} {1969-12-31 23:59:59.999}
+datetest 6.13 {strftime('%Y-%m-%d %H:%M:%f', 0,                 'unixepoch_ms')} {1970-01-01 00:00:00.000}
+datetest 6.14 {strftime('%Y-%m-%d %H:%M:%f', 1,                 'unixepoch_ms')} {1970-01-01 00:00:00.001}
+datetest 6.15 {strftime('%Y-%m-%d %H:%M:%f', 253402300799999,   'unixepoch_ms')} {9999-12-31 23:59:59.999}
+datetest 6.16 {strftime('%Y-%m-%d %H:%M:%f', 253402300799999+1, 'unixepoch_ms')} {NULL}
+
+# This modifier causes the DDDDDDDDDD to beinterpreted not as a Julian 
+# day number as it normally would be, but as Unix Time with millisecond 
+# precision.
+datetest 6.17 {datetime(2459607050,'+1 hour','unixepoch_ms')} {NULL}
+datetest 6.18 {datetime(2459607050,'unixepoch_ms','+1 hour')} {1970-01-29 12:13:27}
+
+for {set i 1} {$i<=100} {incr i} {
+  set x [expr {int(rand()*0xfffffffff)-0xffffffff}]
+  datetest 6.19.$i "unixepoch_ms($x,'unixepoch_ms')==$x" {1}
+}
 
 finish_test

I think that a unixepoch_us(...) function would be pretty much just as useful for the use cases seen in this thread. But it would have use also for more fine-grained timestamping.

To the objection that system times are not known to the microsecond, I would only note that they are rarely accurate to the millisecond either. An accuracy disclaimer would attach in any case.

I agree, but microsecond precision timestamps aren't supported by SQLite yet. The xCurrentTimeInt64() method at the VFS level is only accurate to the millisecond, so unixepoch_us(...) would require more changes

Docs: xCurrentTimeInt64()

What if there is a new function timestamp() that works like unixepoch() except that it returns a floating point value instead of an integer. Then if you want milliseconds you can say:

SELECT 1000.0*timestamp();

I think that would get it halfway there, but would have similar issues.

For one, in practice you would need to constantly CAST() the returned value for common datetime operations. Like if you wanted the number of milliseconds between two columns you’d need to CAST(timestamp(foo * 1000.0) - timestamp(bar * 1000.0) as integer), to remove the floating digits. Or if you wanted the seconds of seconds between two columns, then CAST(timestamp(foo) - timestamp(bar) as integer). Similar for the number of minutes, hours, days, etc.

Additionally, if it was named timestamp(), I would think that it would returns a similar value to current_timestamp. The date() function returns YYYY-MM-DD like current_date, time() and returns HH:MM:SS like current_time. So I’d find it counter-intuitive if current_timestamp returned a string YYYY-MM-DD HH:MM:SS while timestamp() returns a number.

I think the best benefit of a unixepoch_ms()-like function would be having a millisecond-precise builtin datetime function that can be stored as an int. That way, it’ll take up less space than julianday() and strftime("%Y-%m-%d %H:%M:%f”), and you woudn't need to constant cast between ints/floats for common duration calculations. If the timestamp() function instead returned the number of milliseconds as an integer I think it would be better, but still odd that it doesn’t match with current_timestamp.

There are currently three time representations:

1. ISO-8601 text
2. Seconds since 1970-01-01
3. Days since the Julian epoch, -4713-11-24 12:00:00

You are proposing to add a fourth: Milliseconds since 1970.

So in addition to adding a "unixepoch_ms()" function, we would also need to add a "unixepoch_ms" modifier keyword so that date/times in this new format could be properly decoded.

The "auto" modifier will auto-detect which time format is being used and do the right thing. There is an ambiguity, such that unix timestamps between 1970-01-01 00:00:00 and 1970-03-04 04:38:04 might be confused with a Julian day number. But that ambiguity only exists for a span of less than 64 days from a half century ago. If we add milliseconds since 1970 into the mix, then the ambiguity spans more than 8 years, from 1970-01-01 through 1978-01-11 21:30:14.

So the unixepoch_ms() proposal is more than just adding a new date/time function. This is adding whole new date/time format, with all of the accompanying decoding, interpretation, and documentation requirements.

My timestamp() proposal, on the other hand, does not add a new date/time format. It just adds a way to increase the precision of format 2 to millisecond sub-second accuracy. That seems to me to be less complicated, both for the user and for the implementation.

adds a way to increase the precision of format 2 to millisecond accuracy

Hi Richard. Why do you write millisecond accuracy?

Return whatever the platform's (VFS) supports,
which could be nanosecond or 1/10th microsecond accuracy.

That way, the client decides what accuracy it wants/needs.
E.g. I had to use microseconds, not milliseconds, in my case.

Post edited to change "millisecond" to "sub-second".

The VFS returns the current iJD (milliseconds since the julian epoch). It does this irrespective of the accuracy and precision of the underlying Operating System.

This is the purpose of an "obfuscation layer". Its purpose is to make disparate things look the same so that the majority of the code can be "architecture independent". Only the "obfuscation layer" needs to be changed to support a new "underneath" which will adapt the "over above" to use the "underneath" via the "least common set of methods" the "obfuscation layer" present to the "over above".

In the common parlance this is called Virtualization. Some (very) minimal set of fuctionality is chosen (the Virtual Interface), and all programming is done against the Virtualized Interface. When a new "bottoms" is required, it is merely a matter of adapting the lower interface of the Virtualization layer to the new bottoms. The tops remains unchanged.

The underlying problem is that there is no "equivalency" between format #1, format #2, and format #3 -- you cannot express all the vales in all the formats because one of them is ill-conceived in that it is incapable of expression all the values.

Format #1 recognizes the possibility of fractional seconds, as does format #3. Format #2 does not. Format #2 is defective.

THe simplist fix is to have format #2 capable of returning fractional seconds, then the three formats will actually be interchangeable.

Note that over the entire 20,000 year span every value can be represented accurately in each format, except format #2, which is currently broken).

Format #2 does recognize fractional seconds. It is just that the unixepoch() function does not generate them. My proposal for timestamp() is really just shorthand for unixepoch_with_fractional_seconds().

Yes, and it is correct. However, that means that the "unixepoch" function does not, in fact, return a unixepoch stamp.

The "proper" solution would be to have unixepoch return a floating point number precise to the millisecond (3 decimal places), and for an additional function (called unixseconds or whatever you want) that returns the truncated unixepoch number as an integer.

Of course, it may be too late for that.

The "proper" solution would be to have unixepoch return a floating point number

That was my original thought. But it might break code that already uses unixepoch() and that expects an integer value. Unixepoch() was added with version 3.38.0, 14 months ago. It is relatively new, and maybe we could get away with changing its behavior in this way. But it seems safer to add a new function.

Joe suggested a new modifier argument. So perhaps we could have:

• unixepoch()
• unixepoch('now')

Continue to return an integer, but then

• unixepoch('now','fp')

(or similar) returns a floating point value that expresses the current timestamp to millisecond precision, due to the extra 'fp' argument. I could probably rig it so that

• unixepoch('fp')

Is an shorthand alias for unixepoch('now','fp').

To be precision, Joe's actual suggestion was to add a 'millisec' modifier that caused unixepoch() to return an integer millisecond value instead of a value in seconds. But that gets us back to having four different representations of time, which I would like to avoid.

When I re-wrote date.c so that it is always localized, I had the following requirements:

1. All "datetime values" are a point-in-time relative to UT.
2. All representations have the same precision (millisecond)
3. Unlocalized (naive) datetime values can only be created from text strings. All epoch numbers are relative to UT.
4. "stamp" functions only output properly referenced stamps (that is, relative to UT)

The distribution date.c violates all of these constraints.

Once stability was achieved (by compliance with the 4 rules above) adding full IANA timezone support was rather trivial.

A new alternative proposal is now on the subsec-modifier branch. On this branch, there is a new modifier keyword "subsecond" (may be abbreviated as just "subsec"). When that modifier is present, it changes the behavior of some routines to provide subsecond resolution:

• unixepoch() returns a floating point number rather than an integer.
• datetime() shows fractional seconds
• time() shows fractional seconds
• strftime('%s') shows fractional seconds

There is also special handling such that if the first argument is 'subsec' (or 'subsecond') then the time is assumed to be 'now'. Thus you can say:

SELECT unixepoch('subsec');

To get the number of seconds since 1970, as a floating point value. The above is equivalent to "unixepoch('now','subsec')".

The new modifier is a no-op on routines like julianday() which already provide subsecond time resolution and routines like date() which inherently have a time granularity greater than one second.

This alternative design has the advantage that it does not add another time format. The number of time formats is kept at three. See post 0d3926a2b79c7f1d for more on this concern. This proposal also avoids adding any new functions - it only extends the behavior of existing functions.

No test cases have been added yet. This is just a proposal.

The number of time formats is kept at three

But there is only one datetime format, the integer count of milliseconds since the Julian Epoch at some arbitrary meridian. The the unixepoch number, julianday number, and the ISO string formats, are merely "equivalent renderings" of this single time format.

Unfortunately, only julianday passes muster as a truly "equivalent rendering". Neither the unixepoch stamp nor the ISO string formats default to the same precision as the single datetime format (integer milliseconds since the Julian epoch).

The datatime (integer milliseconds since the Julian Epoch at some arbitrary meridian) is also not based on UT, and the outputs of the "equivalent rendersings" julianday, unixepoch, and the datetime (text) format are also not relative to UT nor (other than julianday) even capable of expressing the underlying single datetime formats precision.

I am in favor of this proposal! I do wish that milliseconds since epoch was its own function that returns an integer (since now I'll probably call cast(unixepoch('subsec) * 1000 as integer) all the time), but a subsec modifier is a happy compromise.

I especially like having it in datetime() and time().

I do not mean to open an argument here. However ...

The use of an additional modifier integrates into the existing date/time function interfaces much more cleanly, and allows several of them to have enhanced resolution. From an apparent complexity perspective, it is a clear win. This goes for complexity exposed to users, needed for the documentation, and code alteration.

Anybody who still laments having to type " 'subsec' " instead of having a new function name to say the same thing can just tell themselves, "I'm taking a hit here (typing 9 characters) for sake of interface orthogonality."

Hey, just wanted to bump this: any chance it can make it into 3.42? I know the changelog isn't complete yet, but I'd love to see this in the upcoming release! https://sqlite.org/draft/releaselog/3_42_0.html

Thanks for the reminder. The 'subsecond' modifier for datetime functions will be in the 3.42.0 release.