Geek Answers Handbook

NOW () stable function?

If i do

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

Will the two fields always be the same in both columns?

Same for

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

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

Please note that this is a theoretical question, not a work-around question.
I just want to know how and why.

+7
source share
5 answers

Postgres now() always returns a timestamp that marks the start of a transaction.

So, for your second example, all four lines will have the same time value.

If you want to have a “real” timestamp, you must use clock_timestamp() .

For more information, see the manual:

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

+10
source

If your request does not take much time, the change that all values ​​are the same is quite large. I would not rely on it. The best approach is to first insert the value into the variable and then use this variable several times.

+4
source

If you should have them anyway, you'd better define the now variable first and set it for all columns.

+2
source 
 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 your database supports NOW () and you want unique date time values, your query will generate it, although the time changes will be short.

+2
source

It turns out that there was already 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 start of the statement and never changes until the execution is complete, as confirmed by the documentation. Like PostgreSQL, there is another function in MySQL that returns the actual timestamp, one that corresponds to the evaluation time, indicates the specific part that contains this function, and this SYSDATE() function.

Literature:

0
source

More articles:


All Articles