Is NOW() a stable function?

Question

If I do

INSERT INTO table1 (datetime1, datetime2) VALUES (NOW(),NOW())

Will the two fields always be identical in both columns?

Ditto for

INSERT INTO table1 (datetime1, datetime2) VALUES (NOW(),NOW())
                                                ,(NOW(),NOW())

Will all four database entries have the same value, or is it possible that row1 <> row2?

Note this is a theoretical question rather than a work-around question.
I just really want to know the how and why.

What RDBMS is this for? It is not generic. SQL Server doesn't have `NOW()`. But if you are interested in a SQL Server answer for `getdate()` it is [No, they will not always be the same](http://stackoverflow.com/questions/4056355/selecting-getdate-function-twice-in-a-select-list-same-value-for-both/6043910#6043910) — Martin Smith, Sep 01 '11 at 13:10
@Martin, many thanks for the comment, however my question is slightly different from that one. Most SQL servers precalculate the result of a function that is used in a select/update/insert statement in order to speed up a query. — Johan, Sep 01 '11 at 13:17
Yes SQL Server precalculates each instance of the function reference. So if you do `SELECT GETDATE(), GETDATE() FROM Table` the `GETDATE()` function is called twice regardless of number of rows. It seems to have no logic to call it once and use the same result across both columns however. — Martin Smith, Sep 01 '11 at 13:20
@Martin thanks, that's very informative, I was wondering because you can use the now() function as an salt value like so: `INSERT INTO table1 (who, when, key) VALUES ('user1', now(), SOMESORTOFHASH(CONCAT(NOW(),'secret')))` it would really suck if that doesn't match! — Johan, Sep 01 '11 at 13:25
DB2 (on the iSeries, at least) specifically calls this out as something it _does_ do. Under DB2, all values would share the same clock reading. — Clockwork-Muse, Sep 01 '11 at 16:19
Seems like it would be prudent to not rely on the underlying behavior and just grab the now() value into a variable to ensure the behavior you want. — JohnFx, Oct 07 '11 at 15:08

score 11 · Accepted Answer · answered Sep 01 '11 at 13:20

With Postgres now() always returns the timestamp which denotes the beginning of the transaction.

So for your second example all four rows will have the same timestamp value.

If you want to have the "real" timestamp you have to use clock_timestamp().

More details are in the manual:

http://www.postgresql.org/docs/current/static/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

score 4 · Answer 2 · answered Sep 01 '11 at 13:16

4

If your query does not take to long, the change that all values are the same is pretty high. I would not rely on it though. A better approach is putting the value in a variable first and than use that variable multiple times.

answered Sep 01 '11 at 13:16

Bas Slagter

9,831
7
47
78

thank you for your time, IMO it is not an answer to the question. It appears that the answer differs between different databases and I'm guessing the answer to subquestion 1 does not always match the answer to subquestion 2. Getting an answer to **that** would be interesting. – Johan Sep 01 '11 at 14:15

score 2 · Answer 3 · answered Sep 01 '11 at 13:10

2

If you must have them all the same, you would do better defining a now variable first, and setting all of the columns to that.

answered Sep 01 '11 at 13:10

Schroedingers Cat

3,099
1
15
33

@Schoedinger, that is **not** an answer to the question! – Johan Sep 01 '11 at 13:14
@Johan: Actually, it is an answer (no, they might not be the same) **as well as** a way to guarantee the values are all the same. – Yuck Sep 01 '11 at 13:18
@Yuck: that depends on the DBMS in use. Postgres makes sure that all four calls to `now()` return the same value – Sep 01 '11 at 13:23
I understood the OP to be asking whether the SQL they posted would always have the same values in their fields, and I responded to that. I realise that there was also a question on the integrity of the function which I did not address. – Schroedingers Cat Sep 01 '11 at 13:25
1

@a_horse_with_no_name - as the OP did not mention postgres, while this is a valid comment re postgres, a wider answer seems to make some sense. – Schroedingers Cat Sep 01 '11 at 17:10
@Schroedingers Cat: you are absolutely right. I just wanted to point out, that this "workaround" might not really be necessary for certain DBMS (I didn't check others) – Sep 01 '11 at 17:15

score 2 · Answer 4 · answered Sep 01 '11 at 13:17

DECLARE @Timestamp DATETIME
SELECT @Timestamp = getDate()
INSERT INTO table1 (datetime1, datetime2) VALUES (@Timestamp, @Timestamp)

The above will create equal values in both fields in the table, if that is what you want.

If the database you are using supports NOW(), and you want unique date time values, your query will produce it, though the time changes will be short.

score 0 · Answer 5 · edited May 23 '17 at 12:09

Turns out there has already been a question similar to yours, but it was specifically about MySQL.

As follows from Richard's answer, the value returned by NOW() is fixed at the beginning of the statement's execution and never changed till the execution is complete, which is confirmed by documentation. Similarly to PostgreSQL, there's another function in MySQL too that returns the actual timestamp, the one that corresponds to the time of the evaluation the statement's particular part that contains the function, and that function is SYSDATE().

References:

Is NOW() a stable function?

5 Answers5