SQLite Forum

Is date/time atomic per statement/transaction?
Login

Is date/time atomic per statement/transaction?

(1) By anonymous on 2020-09-07 16:19:25 [link] [source]

Does the date/time refer to the exact same timestamp for the whole duration of the statement and/or transaction?

Simple single statement example: SELECT date('now'),time('now')

If system clock changes between SQLite's processing of date('now') and time('now') function calls, what will I get?

Will it be whatever is 'grabbed' from the system clock at the point of execution of each function?

Couldn't find any mention about it in here

(2) By Kees Nuyt (knu) on 2020-09-07 18:39:24 in reply to 1 [source]

It is atomic per statement (not per transaction), as demonstrated by this experiment:

$ strace -c sqlite3 :memory: \
"SELECT date('now'),time('now')"
2020-09-07|18:23:18
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.20    0.000020          20         1           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.010088                   282         5 total

$ strace -c sqlite3 :memory: \
"SELECT date('now'),time('now')" \
"SELECT date('now'),time('now')"
2020-09-07|18:24:43
2020-09-07|18:24:43
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.54    0.000052          26         2           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.009686                   284         5 total

$ strace -c sqlite3 :memory: \
"BEGIN TRANSACTION" \
"SELECT date('now'),time('now')" \
"SELECT date('now'),time('now')" \
"COMMIT"
2020-09-07|18:32:26
2020-09-07|18:32:26
% time     seconds  usecs/call     calls    errors syscall
------ ----------- ----------- --------- --------- ----------------
  0.94    0.000052          26         2           gettimeofday
------ ----------- ----------- --------- --------- ----------------
100.00    0.005525                   284         5 total

Note: counts of other function calls removed for brevity.

-- 
Regards,
Kees Nuyt

(3) By Richard Hipp (drh) on 2020-09-07 18:58:18 in reply to 1 [link] [source]

From the point of view of a prepared statement, the time is held constant for the duration of a single call to sqlite3_step(). So if you say:

   SELECT datetime('now'), slow_function(), datetime('now');

Then you are guaranteed to get the same answer in the first and third columns, regardless of how many seconds (or hours) it takes to compute slow_function(). Similarly, if you do:

   UPDATE table SET mtime=julianday('now');

Because the entire UPDATE runs within a single sqlite3_step() call, all mtime columns will get exactly the same value. This is guaranteed. However, if you do something like this:

   WITH RECURSIVE c(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM c WHERE x<10)
   SELECT datetime('now',printf('+%d seconds',x-x)) FROM c;

Then you are going to have to invoke sqlite3_step() 10 different times, one for each row of output. And you might to get a different answer for each row, particularly if you insert a delay in between each sqlite3_step() call. But on the other hand, you might not. SQLite, at its option, can choose to evaluate multiple rows on the first sqlite3_step() call, then hand them back to you one by one on subsequent calls. If it chooses to do things that way, then all the timestamps will be the same. But if it computes one row at a time, then you will get different answers if you delay between each sqlite3_step() call.

Note the the use of the argument "printf('+%d seconds',x-x)" to datetime(). That extra logic is a no-op in the sense that it does not change the answer. But it does trick the query planning into thinking that the datetime() function depends upon the value of x so that datetime() must to be evaluated separately for each row in the output. If you omit that bit of magic, SQLite recognizes that just "datetime('now')" is independent of the x value, and it might factor that expression out, evaluate it just once, and reuse the same result over and over again, regardless of how many times you call sqlite3_step().

So, in summary, "it's complicated".

(4) By Keith Medcalf (kmedcalf) on 2020-09-07 23:09:39 in reply to 1 [link] [source]

The short answer is that the iCurrentDate of a "statement" (VDBE program) is "step stable" because it is reset to 0 each time that sqlite3_step is called on a statement. All accesses to iCurrentDate within that execution/step refer to the same iCurrentDate which is recorded the first time it is accessed in that execution/step.

In my own private branch of the code I have a patch which only resets iCurrentDate to 0 on "initial entry" into the VDBE program (that is, when the program counter is 0) which will make iCurrentDate "statement stable", meaning that iCurrentDate is set first time it is accessed by the statement and iCurrentDate remains unchanging until the statement is reset, even across multiple calls to step.

Making iCurrentDate "transaction stable" would require storing iCurrentDate in the connection context and adjusting the autocommit code to reset/clear the connection iCurrentDate at transaction end and the sqlite3StmtCurrentTime to use the connection iCurrentDate -- I have not looked into this and, although the changes are probably trivial, I do not think anyone has ever requested it -- I don't know if anyone has ever requested that iCurrentDate be "statement" stable rather than "step" stable.

The patch I have installed follows and creates a new define SQLITE_NOW_STABILITY_STMT which causes iCurrentDate to be statement stable rather than step stable.

Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -703,10 +703,13 @@
   }
   assert( p->rc==SQLITE_OK || (p->rc&0xff)==SQLITE_BUSY );
   testcase( p->rc!=SQLITE_OK );
   p->rc = SQLITE_OK;
   assert( p->bIsReader || p->readOnly!=0 );
+#ifdef SQLITE_NOW_STABILITY_STMT // Only reset iCurrentDate at start of statement
+  if (p->pc == 0)
+#endif
   p->iCurrentTime = 0;
   assert( p->explain==0 );
   p->pResultSet = 0;
   db->busyHandler.nBusy = 0;
   if( AtomicLoad(&db->u1.isInterrupted) ) goto abort_due_to_interrupt;

(6) By anonymous on 2020-09-11 15:08:45 in reply to 4 [link] [source]

If you are reluctant or unable to apply this clever patch you could use the following in the CLI:

Create Table Data1 ( key, ts, saved_ts, info);
Create Table Data2 ( key, ts, saved_ts, info);
Begin;
.parameter set @SAVED_TS CURRENT_TIMESTAMP
Insert Into Data1 Values (1, CURRENT_TIMESTAMP, @SAVED_TS, 'This is a test');
Select * From Generate_Series(0,40000,1);
Insert Into Data2 Values (2, CURRENT_TIMESTAMP, @SAVED_TS, 'This is another test');
Commit;
.mode box
Select * from Data1;
Select * from Data2;

The Generate_Series() is just to slow down things between the two Inserts.
This is the result:

┌─────┬─────────────────────┬─────────────────────┬────────────────┐
│ key │         ts          │      saved_ts       │      info      │
├─────┼─────────────────────┼─────────────────────┼────────────────┤
│ 1   │ 2020-09-11 14:36:10 │ 2020-09-11 14:36:10 │ This is a test │
└─────┴─────────────────────┴─────────────────────┴────────────────┘
┌─────┬─────────────────────┬─────────────────────┬──────────────────────┐
│ key │         ts          │      saved_ts       │         info         │
├─────┼─────────────────────┼─────────────────────┼──────────────────────┤
│ 2   │ 2020-09-11 14:36:14 │ 2020-09-11 14:36:10 │ This is another test │
└─────┴─────────────────────┴─────────────────────┴──────────────────────┘

Anywhere else than the CLI you would obviously not have the .parameter statement, but you could use a Select for CURRENT_TIME or any other date/time combination, save the result and use that in subsequent Inserts.

(5) By Keith Medcalf (kmedcalf) on 2020-09-08 00:32:14 in reply to 1 [link] [source]

iCurrentDate stores the "integer milliseconds since the beginning of the julian epoch" so it stores the "Current Time" to the millisecond within the accuracy of the hardware/system clock.

The system call used to retrieve "now" on non-windows systems returns the current system clock time precise to at least the millisecond (and often with finer ganularity). (Though I suppose it may or may not be updated continuously and may suffer the same granularity issue as windows).

On Windows systems, however, even though the GetSystemTimeAsFileTime API used to retrieve the system clock has a granularity of huns (hundreds of nanoseconds), it is only "updated" once per tick (usually 15 ms depending on the hardware) or as specified by the kernel timer frequency.

The long and the short of it is that the value of "now" as representing the machine concept of the time "right now" is subject to many vagaries of the OS as well.

My local branch of SQLite3 includes a patch to os_win.c that creates a new define SQLITE_USE_PRECISE_TIME that replaces the call to GetSystemTimeAsFileTime with a call to GetSystemTimePreciseAsFileTime so that the returned value does not depend on the system timer frequency. (Note that this API is only available on later versions of Windows).

Index: src/os_win.c
==================================================================
--- src/os_win.c
+++ src/os_win.c
@@ -753,11 +753,15 @@
   { "GetSystemTime",           (SYSCALL)GetSystemTime,           0 },

 #define osGetSystemTime ((VOID(WINAPI*)(LPSYSTEMTIME))aSyscall[29].pCurrent)

 #if !SQLITE_OS_WINCE
+#ifdef SQLITE_USE_PRECISE_TIME
+  { "GetSystemTimeAsFileTime", (SYSCALL)GetSystemTimePreciseAsFileTime, 0 },
+#else
   { "GetSystemTimeAsFileTime", (SYSCALL)GetSystemTimeAsFileTime, 0 },
+#endif
 #else
   { "GetSystemTimeAsFileTime", (SYSCALL)0,                       0 },
 #endif

 #define osGetSystemTimeAsFileTime ((VOID(WINAPI*)( \