Datetime functions (that is, what constitutes "now") was, by default, 
step-stable.  The value is cached within the VDBE (statement object) on its 
first use per-step and retains the same value until the VDBE code yields a row. 
 Re-entry on the next step used to reset "now".

This was changed to per-statement stability and not per-step stability, though 
exactly when this change occurred I do not recall exactly (I believe it was 
made a SLOCHNG function when indexes on functions were added).

As I posted earlier you can patch the VDBE code to ensure that "now" is always 
statement stable rather than step stable by only resetting "now" on initial 
entry to the VDBE program (program counter == 0) rather than unconditionally.  
Of course, the change that made "now" statement stable renders this patch moot.

In no case is now "transaction stable" as the value of now is cached within the 
statement object (VDBE program) and not in the connection object.

This case is where I have "forced" "now" to be statement stable ... although 
removing that patch appears to make no difference.
Note this is with the current head of trunk.

The current source code contains the following comment (search the amalgamation 
for iCurrentTime):

** Return the current time for a statement.  If the current time
** is requested more than once within the same run of a single prepared
** statement, the exact same time is returned for each invocation regardless
** of the amount of time that elapses between invocations.  In other words,
** the time returned is always the time of the first call.

This would indicate that "now" has statement-stability and not 
transaction-stability, which matches with my observations.

import apsw
import apswrow
import time
db = apsw.Connection(':memory:')
db.cursor().execute('begin immediate');
while True:
 for row in db.cursor().execute('select value, julianday() from generate_series 
where start=1 and stop=10'):
  print row

Row(value=1, julianday=2458086.2071575928)
Row(value=2, julianday=2458086.2071575928)
Row(value=3, julianday=2458086.2071575928)
Row(value=4, julianday=2458086.2071575928)
Row(value=5, julianday=2458086.2071575928)
Row(value=6, julianday=2458086.2071575928)
Row(value=7, julianday=2458086.2071575928)
Row(value=8, julianday=2458086.2071575928)
Row(value=9, julianday=2458086.2071575928)
Row(value=10, julianday=2458086.2071575928)
Row(value=1, julianday=2458086.2072733566)
Row(value=2, julianday=2458086.2072733566)
Row(value=3, julianday=2458086.2072733566)
Row(value=4, julianday=2458086.2072733566)
Row(value=5, julianday=2458086.2072733566)
Row(value=6, julianday=2458086.2072733566)
Row(value=7, julianday=2458086.2072733566)
Row(value=8, julianday=2458086.2072733566)
Row(value=9, julianday=2458086.2072733566)
Row(value=10, julianday=2458086.2072733566)
Row(value=1, julianday=2458086.2073891205)
Row(value=2, julianday=2458086.2073891205)
Row(value=3, julianday=2458086.2073891205)
Row(value=4, julianday=2458086.2073891205)
Row(value=5, julianday=2458086.2073891205)
Row(value=6, julianday=2458086.2073891205)
Row(value=7, julianday=2458086.2073891205)
Row(value=8, julianday=2458086.2073891205)
Row(value=9, julianday=2458086.2073891205)
Row(value=10, julianday=2458086.2073891205)
Row(value=1, julianday=2458086.207504896)
Row(value=2, julianday=2458086.207504896)

The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.

>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>] On Behalf Of Simon Slavin
>Sent: Tuesday, 28 November, 2017 09:02
>To: SQLite mailing list
>Subject: Re: [sqlite] Datetime / Transactions / CLI
>On 28 Nov 2017, at 3:50pm, wrote:
>> Can someone point me to the documentation for behaviour of
>> functions inside transactions? In my code it appears time is
>Correct.  The value of 'now' is frozen at the time a transaction
>begins.  This is to ensure that if many rows are created/updated
>(perhaps even in different tables) they are all assigned the same
>If you need to record, instead, the timestamp that data was entered
>into your user-interface, then you need to record that time in your
>programming language, not rely on when the data hits SQL.
>> The command-line client on the other hand doesn't behave the same
>>      sqlite> begin immediate;
>>      sqlite> select julianday();
>>      julianday()
>>      ----------------
>>      2458086.15509343
>>      sqlite> select julianday();
>>      julianday()
>>      ----------------
>>      2458086.15511422
>> I'm guessing that the CLI is kind of broken
>That’s an interesting observation and I am surprised by it.
>sqlite-users mailing list

sqlite-users mailing list

Reply via email to