Datetime / Transactions / CLI

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Datetime / Transactions / CLI

nomad
Can someone point me to the documentation for behaviour of date/time
functions inside transactions? In my code it appears time is frozen.
The command-line client on the other hand doesn't behave the same way:

        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, given that the in-code
version matches with what PostgreSQL does.

On a related note, is a user-defined function the only way to get the
real system time within a transaction? Would the developers consider an
additional "real" (or similar) datetime modifier?

--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Datetime / Transactions / CLI

Simon Slavin-3


On 28 Nov 2017, at 3:50pm, [hidden email] wrote:

> Can someone point me to the documentation for behaviour of date/time
> functions inside transactions? In my code it appears time is frozen.

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 timestamp.

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 way:
>
> 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.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Datetime / Transactions / CLI

Keith Medcalf

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
  time.sleep(1)

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-
>[hidden email]] 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, [hidden email] wrote:
>
>> Can someone point me to the documentation for behaviour of
>date/time
>> functions inside transactions? In my code it appears time is
>frozen.
>
>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
>timestamp.
>
>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
>way:
>>
>> 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.
>
>Simon.
>_______________________________________________
>sqlite-users mailing list
>[hidden email]
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Datetime / Transactions / CLI

Simon Slavin-3


On 28 Nov 2017, at 5:34pm, Keith Medcalf <[hidden email]> wrote:

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

You’re right, I was wrong.  Thanks for the correction.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Datetime / Transactions / CLI

nomad
In reply to this post by Keith Medcalf
On Tue Nov 28, 2017 at 10:34:03AM -0700, Keith Medcalf wrote:

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

Thanks, that is quite informative. My original observations
(transaction stable times) were wrong - I was looking at a bunch of
trigger actions and forgot they were all part of one statement.

My original comments/requests to the developers still apply though:

    - Can the documentation be updated to include something like
      Keith's description; and
    - Can we have some kind of control over whether 'now' is
      step-/statement-/real-time

Regards,
Mark
--
Mark Lawrence
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Datetime / Transactions / CLI

Stephen Chrzanowski
Although I don't do many long length transactions for date and times, I
kind of like the idea of having the control of over how the library allows
you to chose which way the dates and times are going to work. Default to
the current model, of course, to allow for backward compatibility, but,
either allow for a pragma to be set a particular way, or, a new compile
time directive to configure for the required mode.

On Tue, Nov 28, 2017 at 2:25 PM, <[hidden email]> wrote:

>
>
> My original comments/requests to the developers still apply though:
>
>     - Can the documentation be updated to include something like
>       Keith's description; and
>     - Can we have some kind of control over whether 'now' is
>       step-/statement-/real-time
>
> Regards,
> Mark
> --
> Mark Lawrence
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users