Eric Bergen <ebergen@stripped> wrote on 06/09/2005 12:56:59 PM:
> How about something like this:
> mysql> select @t := now();
> +---------------------+
> | @t := now() |
> +---------------------+
> | 2005-06-09 09:55:49 |
> +---------------------+
> 1 row in set (0.00 sec)
> mysql> insert delayed into t set t = @t;
> Query OK, 1 row affected (0.00 sec)
> mysql> select * from t;
> +---------------------+
> | t |
> +---------------------+
> | 2005-06-09 09:55:49 |
> +---------------------+
> 1 row in set (0.01 sec)
>
> This way you get the current time of the call and it doesn't matter how
> long the insert delayed sits for.
> Jochem van Dieten wrote:
> >On 6/9/05, Jeremiah Gowdy wrote:
> >
> >
> >>>Does this seem to break SQL / application logic in some fashion?
> >>>
> >>>
> >>>Not worse then it is currently broken :)
> >>>
> >>>According to the SQL standard CURRENT_TIMESTAMP, which in MySQL is a
> >>>synonym for NOW(), is supposed to have a value that does not change
> >>>during a transaction. At which point during the transaction that
value
> >>>is 'sampled' is implementation defined. (ISO/IEC 9075:2003-2 section
> >>>6.31)
> >>>
> >>>Since both NOW() and INSERT DELAYED are MySQL extensions I don't
> >>>particularly care how they behave/interfere, but I would prefer any
> >>>solution/hack not to complicate MySQL ever becomming standard
> >>>compliant in this regard (and standard compliance is an official
> >>>goal).
> >>>
> >>>
> >>Does the standard specify when the timestamp is evaluated?
> >>
> >>
> >
> >During the transaction.
> >
> >
> >
> >
> >>I agree that it might be better for it to be a seperate function, but
since
> >>DELAYED isn't part of the standard, I'm not sure there's anything that
keeps
> >>an implementation from evaluating the CURRENT_TIMESTAMP for a query
upon
> >>receipt of the query from the network, rather than when the SQL
statement is
> >>evaluated.
> >>
> >>
> >
> >Let me reiterate:
> >"Since both NOW() and INSERT DELAYED are MySQL extensions I don't
> >particularly care how they behave/interfere".
> >
> >
> >
> >
> >>If I wrote a SQL server from scratch, would this not
> >>be a valid implementation, to timestamp upon network receive of a
complete
> >>query, rather than upon finding the CURRENT_TIMESTAMP (or NOW())
function
> >>while parsing a query?
> >>
> >>
> >
> >That depends on some more implementation issues: perceivably your
> >network receive could even be before the start of the transaction.
> >Evaluate CURRENT_TIMESTAMP only once per transaction, between the
> >start of the transaction and the end of the transaction.
> >
> >Jochem
> >
> >
> >
The problem with that is that you have just doubled the query count at the
central logging server. That's a lot of traffic it can probably do
without.
I like the QNOW() approach. (Use an extension, the new function, to deal
with a side effect of an extension, DELAYED. It's a universal balance kind
of thing.)
Some alternative names: QUEUEDNOW(), QUEUEDTIMESTAMP(), RECEIVEDTIME(),
RECEIVEDTIMESTAMP(), ARRIVALTIMESTAMP()
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.