10

I met a query similar to

INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())

having column definitions

| Created   | datetime |
| Updated   | datetime |

Will in this case MySQL set the NOW() value to the current time and return that same values to both calls, or will that query run the (improbable) risk of having two slightly different times for Created and Updated?

Note: I can't change the field types.

22

The time returned by NOW(), and other date time functions, is derived from the start time of the query. The THD class here is used to contain all the information for the connection. The NOW() function implementation grabs this value and returns it into the now_time structure.

MySQL docs for NOW() also state:

NOW() returns a constant time that indicates the time at which the statement began to execute. ...

You will always get the same NOW() value from anywhere in the query.

MariaDB [test]> select now(),sleep(10),now();
+---------------------+-----------+---------------------+
| now()               | sleep(10) | now()               |
+---------------------+-----------+---------------------+
| 2021-03-22 14:17:05 |         0 | 2021-03-22 14:17:05 |
+---------------------+-----------+---------------------+

Worth noting, that this isn't per transaction:

MariaDB [test]> start transaction
    -> ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [test]>  select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:20:53 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [test]> select sleep(2);
+----------+
| sleep(2) |
+----------+
|        0 |
+----------+
1 row in set (2.00 sec)

MariaDB [test]> select now();
+---------------------+
| now()               |
+---------------------+
| 2021-03-22 14:21:00 |
+---------------------+
1 row in set (0.00 sec)
3
  • This is the answer I was expecting, thanks. My tests (see below) empirically tend to that same conclusion (but your idea of adding sleep() in between is simpler, and more conclusive). – Breaking not so bad Mar 22 at 3:25
  • or you could go trickier structures like select now(),sleep(2), (select sleep(2) + now()) union all select now(), sleep(2), now(); same result however. – danblack Mar 22 at 3:35
  • 1
    If you have binary logging turned on, you can see the timestamp of each command logged along the command. This timestamp is used for NOW(). – rexkogitans Mar 22 at 12:57
17

Another aspect: SYSDATE() can be different, but NOW() cannot.

mysql> select now(6), sysdate(6), now(6), sysdate(6);
+----------------------------+----------------------------+----------------------------+----------------------------+
| now(6)                     | sysdate(6)                 | now(6)                     | sysdate(6)                 |
+----------------------------+----------------------------+----------------------------+----------------------------+
| 2021-03-21 22:02:28.983211 | 2021-03-21 22:02:28.983368 | 2021-03-21 22:02:28.983211 | 2021-03-21 22:02:28.983370 |
+----------------------------+----------------------------+----------------------------+----------------------------+
1 row in set (0.00 sec)

I'm using (6) so we can see the time down to the microsecond. Note how the NOW calls are identical and the smallest values. Meanwhile, the SYSDATE calls are later and not the same as each other.

----- 2006-03-31 5.0.20 & not yet released 5.1.8 -- Functionality Added or Changed -- -----

Added the --sysdate-is-now option to mysqld to enable SYSDATE() to be treated as an alias for NOW(). See Date and Time Functions. (Bug #15101)

----- 2005-09-22 5.0.13 Release Candidate -- Bugs Fixed -- -----

The SYSDATE() function now returns the time at which it was invoked. In particular, within a stored routine or trigger, SYSDATE() returns the time at which it executes, not the time at which the stored routine or triggering statement began to execute. (Bug #12480)

2
  • Nice addition . – Breaking not so bad Mar 22 at 5:13
  • 6
    One obscure practical use: To measure replication delay. The NOW() value is passed through replication, not replicated. SYSDATE() is recomputed on the Replica. – Rick James Mar 22 at 16:19
2

Didn't find the answer from @nbk satisfying enough for my needs (especially "No, as long as you have no micro seconds in your datetime and a really very slow server, it could be possible").

So I simulated a kind of very slow server.

My Linux desktop MB has 6 cores, I made this C program

#define COUNT 10000000000L

double busy() {
     volatile double z = rand() / M_PI;

     for(long l=0 ; l<COUNT ; l++) {
          z += sqrt(M_PI);
     }
     return z;
}

and in the main

 int n = atoi(*++argv); // input 10 

 while(n--) {
      if ( ! fork()) {
            printf("%d => %lf\n", n, busy());
            exit(0);
      }
 }

 pid_t wpid;
 int status = 0;
 while ((wpid = wait(&status)) > 0);

That makes n threads very busy. With n = 10, the desktop was really busy...

While that was running (as root, to ensure the system doesn't try to salvage some resources for itself :) I had prepared this earlier

create table a (num int auto_increment primary key,
   d1 datetime(6), d2 datetime(6), d3 datetime(6), d4 datetime(6));

and a file made of 1000 lines

insert into a values(null,now(6),now(6),now(6),now(6));

So when the PC was running 10 threads busy() on 6 cores [all were at 100%], I injected the 1000 inserts into the table, took a bit longer than usual, then

select * from a where d1 <> d2 or d1 <> d3 or d1 <> d4;
Empty set (0.00 sec)

Yes! MySQL is really well made! (seriously, I'm always amazed at the quality in algorithms and system programming behind this DBMS).

6
  • Isn't MySQL open source? Why not just check the code base? There's probably a unit test in there somewhere, for this. – jwdonahue Mar 23 at 4:40
  • Well, it took me less than 5 minutes to write this. Downloading the huge code from MySQL and looking in the huge code base would likely take more than that... – Breaking not so bad Mar 23 at 14:41
  • Except that it suffers from the "works for me" syndrome, without actually understanding how it is designed and implemented. The problem with black-box testing, is you're discovering probable outcomes. @danblack's answer, has a reference to the code. – jwdonahue Mar 23 at 19:05
  • @jwdonahue I don't know from what you suffer :) but as said, the only answer at the time was not what I was expecting, so I covered the case they mentioned, "very slow server", and had a lot of fun doing my own tests (then with 20k lines and 20 threads, very low probability of a less than a microsecond difference). It was indeed empirical, until a more conclusive answer was posted, danblack, that I duly commented and accepted. – Breaking not so bad Mar 24 at 6:03
  • I think I suffer from 30 years of development work, and nearly half of that in test. I've seen tests like this run for days, turning up false results, and then fail when compiled or run on a different machine, or against a different database instance. Worse yet, is when you do drill into the code and find it's written to do as you expect, and then two months later, that's not how it works anymore. – jwdonahue Mar 24 at 18:25
1

No, the will be no difference

as MysQL Writes in the manual

NOW() returns a constant time that indicates the time at which the statement began to execute.

This seems to be a diffrent behaviour as all other functions, which are run column by column.

CREATE TABLE mytable (id INT AUTO_INCREMENT PRIMARY KEY, Created DATETIME , Updated DATETIME)
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(), NOW())
INSERT INTO mytable (id, Created, Updated) VALUES (null, NOW(3), NOW(3))
SELECT MICROSECOND(Created),MICROSECOND(Updated) FROM mytable
MICROSECOND(Created) | MICROSECOND(Updated)
-------------------: | -------------------:
                   0 |                    0
                   0 |                    0
CREATE TABLE mytable2 (id INT AUTO_INCREMENT PRIMARY KEY, Created DATETIME(3) , Updated DATETIME(3))
INSERT INTO mytable2 (id, Created, Updated) VALUES (null, NOW(3), NOW(3))
SELECT MICROSECOND(Created),MICROSECOND(Updated) FROM mytable2
MICROSECOND(Created) | MICROSECOND(Updated)
-------------------: | -------------------:
              186000 |               186000

db<>fiddle here

2
  • 2
    The answer is not clear. Is it possible or not for NOW() to return different results in the same query? – ypercubeᵀᴹ Mar 24 at 10:06
  • 1
    MySQL docs state that NOW() returns a constant time that indicates the time at which the statement began to execute. so I don't see how you have two different values from the same query. – ypercubeᵀᴹ Mar 24 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.