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*".