94

I need to update 2 datetime columns, and I need them to be exactly the same, using mysql version 4.1.20. I'm using this query:

mysql> update table set last_update=now(), last_monitor=now() where id=1;

It is safe or there is a chance that the columns are update with different time, because of the 2 visible calls to now()?
I don't think that it can be update with different values (I think internally mysql calls now() just once per row or something similar), but I'm not an expert, what do you think?

Update: Second question was extracted here.

Radu Maris
  • 5,648
  • 4
  • 39
  • 54
  • 1
    I suggest that you remove your second question from here, and eventually repost it in a separate post. – Cœur Dec 16 '17 at 15:06

7 Answers7

156

Found a solution:

mysql> UPDATE table SET last_update=now(), last_monitor=last_update WHERE id=1;

I found this in MySQL Docs and after a few tests it works:

the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Community
  • 1
  • 1
Radu Maris
  • 5,648
  • 4
  • 39
  • 54
7

Mysql isn't very clever. When you want to use the same timestamp in multiple update or insert queries, you need to declare a variable.

When you use the now() function, the system will call the current timestamp every time you call it in another query.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Olivier
  • 71
  • 1
  • 1
5

MySQL evaluates now() once per statement when the statement commences execution. So it is safe to have multiple visible now() calls per statement.

select now(); select now(), sleep(10), now(); select now();
+---------------------+
| now()               |
+---------------------+
| 2018-11-05 16:54:00 |
+---------------------+
1 row in set (0.00 sec)

+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2018-11-05 16:54:00 |         0 | 2018-11-05 16:54:00 |
+---------------------+-----------+---------------------+
1 row in set (10.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2018-11-05 16:54:10 |
+---------------------+
1 row in set (0.00 sec)
Rich Andrews
  • 1,590
  • 8
  • 12
1

You can store the value of a now() in a variable before running the update query and then use that variable to update both the fields last_update and last_monitor.

This will ensure the now() is executed only once and same value is updated on both columns you need.

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
1

You can put the following code on the default value of the timestamp column: CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, so on update the two columns take the same value.

blackpanther
  • 10,998
  • 11
  • 48
  • 78
Danny
  • 11
  • 1
0

If you really need to be sure that now() has the same value you can run two queries (that will answer to your second question too, in that case you are asking to update last_monitor = to last_update but last_update hasn't been updated yet)

you could do something like:

mysql> update table set last_update=now() where id=1;
mysql> update table set last_monitor = last_update where id=1;

anyway I think that mysql is clever enough to ask for now() only once per query.

blackpanther
  • 10,998
  • 11
  • 48
  • 78
sathia
  • 2,192
  • 2
  • 24
  • 42
0

There are 2 ways to this;

First, I would advice you declare now() as a variable before injecting it into the sql statement. Lets say;

var x = now();
mysql> UPDATE table SET last_update=$x, last_monitor=$x WHERE id=1;

Logically if you want a different input for last_monitor then you will add another variable like;

var y = time();
mysql> UPDATE table SET last_update=$x, last_monitor=$y WHERE id=1;

This way you can use the variables as many times as you can, not only in mysql statements but also in the server-side scripting-language(like PHP) you are using in your project. Remember these same variables can be inserted as inputs in a form on the front-end of the application. That makes the project dynamic and not static.

Secondly if now() indicates time of update then using mysql you can decalre the property of the row as a timestamp. Every time a row is inserted or updated time is updated too.

Wahinya Brian
  • 663
  • 9
  • 18
  • Welcome to SO. Though this is a solution to the problem, it doesn't really answer the question which is "Is there a problem using the mysql built-in NOW() function?". Also you suggest using an external language which the poster may have no control over. Your 2nd suggestion I am unsure of (my recollection of mysql isn't that detailed) but supporting it with links to some docs/examples would give far more credibility. – Martin Nov 14 '18 at 14:34
  • In my 2nd suggestion, if now() is to be used as a timestamp whenever any update takes place, the following link can help out; https://alvinalexander.com/mysql/mysql-update-timestamp-on-update-sql-update-query In my 1st suggestion, each column in a mysql table is independent. Hence data input(like using one variable in the question above) is done independently. However you can compare and contrast data in different columns using operators like >,<,=,!=,!<. This simply means you cant input data into the cols like this; mysql> update table set last_update=last_monitor=now() where id=1; – Wahinya Brian Nov 14 '18 at 15:37