Search code examples
sqldatabase-agnostic

Is NOW() a stable function?

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.

Solution

  • 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