When running UPDATE … datetime = NOW(); will all rows updated have the same date/time?

When you run something similar to:

UPDATE table SET datetime = NOW();

on a table with 1 000 000 000 records and the query takes 10 seconds to run, will all the rows have the exact same time (minutes and seconds) or will they have different times? In other words, will the time be when the query started or when each row is updated?

I’m running MySQL, but I’m thinking this applies to all dbs.

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_now

“NOW() returns a constant time that indicates the time at which the statement began to execute. (Within a stored routine or trigger, NOW() returns the time at which the routine or triggering statement began to execute.) This differs from the behavior for SYSDATE(), which returns the exact time at which it executes as of MySQL 5.0.13. “

Method 2

Assign NOW() to a variable then update the datetime with variable:

update_date_time=now()

now update like this

UPDATE table SET datetime =update_date_time;

correct the syntax, as per your requirement

Method 3

They should have the same time, the update is supposed to be atomic, meaning that whatever how long it takes to perform, the action is supposed to occurs as if all was done at the same time.

If you’re experiencing a different behaviour, it’s time to change for another DBMS.

Method 4

The sqlite answer is

update TABLE set mydatetime = datetime('now');

in case someone else was looking for it.

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating