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

The now() function returns the constant time that exhibits the time at which any statement began to execute. The sysdate() function returns the exact same datetime at which it executed the statement from MySQL 5.0.13.

Suppose if you are updating datetime with now() in triggers or stored procedure, the now() method returns the time at which time the triggering and stored procedure begin to execute.

Here is the demo of update with now(). Let us first create a table. The query to create a table is as follows −

mysql> create table NowDemo
-> (
-> DueDateTime datetime
-> );
Query OK, 0 rows affected (0.51 sec)

Insert records in the table using insert command. The query is as follows −

mysql> insert into NowDemo values('2018-12-19');
Query OK, 1 row affected (0.15 sec)

mysql> insert into NowDemo values('2018-11-10');
Query OK, 1 row affected (0.20 sec)

mysql> insert into NowDemo values('2017-11-21');
Query OK, 1 row affected (0.09 sec)

Check all records from the table using select statement. The query is as follows −

mysql> select *from NowDemo;

Output

+---------------------+
| DueDateTime         |
+---------------------+
| 2018-12-19 00:00:00 |
| 2018-11-10 00:00:00 |
| 2017-11-21 00:00:00 |
+---------------------+
3 rows in set (0.00 sec)

Here is the query to update the datetime column with now() which updates all values.

Case 1 − Using now()

The query is as follows −

mysql> update NowDemo set DueDateTime = now();
Query OK, 3 rows affected (0.12 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Check the updated value using select command. The query is as follows −

mysql> select *from NowDemo;

The following is the output displaying all the rows now has the same datetime −

+---------------------+
| DueDateTime         |
+---------------------+
| 2018-12-20 16:10:00 |
| 2018-12-20 16:10:00 |
| 2018-12-20 16:10:00 |
+---------------------+
3 rows in set (0.00 sec)

Case 2 − Using sysdate()

The query is as follows −

mysql> update NowDemo set DueDateTime = sysdate();
Query OK, 3 rows affected (0.43 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Check the updated value from the table using select statement. The query is as follows −

mysql> select *from NowDemo;

The following is the output displaying all the rows now has the same datetime −

+---------------------+
| DueDateTime         |
+---------------------+
| 2018-12-20 16:10:35 |
| 2018-12-20 16:10:35 |
| 2018-12-20 16:10:35 |
+---------------------+
3 rows in set (0.00 sec)