2

I've made a test. Here is a innodb table:

CREATE TABLE `test_UNIX_TIMESTAMP` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `datefrom` INT(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Here is an insert:

insert into test_UNIX_TIMESTAMP (datefrom) values
(UNIX_TIMESTAMP())
,(UNIX_TIMESTAMP())
,(UNIX_TIMESTAMP())
....many many
,(UNIX_TIMESTAMP());

Here is a query to check is it unique timestamps or not:

select distinct(datefrom) from test_UNIX_TIMESTAMP;

The results are:

After Affected rows: 106 400, we have 1 unique timestamp value in the table.

After Affected rows: 1 170 400 , we still have 1 unique timestamp value in the table.

MySQL server - it's remote SQL server (xeon 1270v3 with 32 ram, mysql have 4Gb cache).

Is "UNIX_TIMESTAMP()" value fixed for large insert queries? Or it's fixed for session? Or i'm just lucky?

nikitasius
  • 99
  • 9
  • When you ran the insert, how long did it take to run? – Shadow Nov 04 '15 at 11:12
  • it's remote server and i'm on ADSL. So 1mil rows it's 24Mb of traffic and it takes 00:03:51 (sent to server and execute). After i see burst iops on server for short time and my client (heidisql) returning me th result with time and how much rows was affected. – nikitasius Nov 04 '15 at 11:23

2 Answers2

2

The MySQL documentation isn't clear on this. However, it does draw a distinction between CURRENT_TIMESTAMP() (or NOW()) and SYSDATE() — the former is evaluated once per query, while the latter is evaluated at every invocation (and is presumably a bit slower).

You can demonstrate this for yourself by calling both either side of a SLEEP instruction:

mysql> select NOW() "a", SYSDATE() "b", SLEEP(2) "_", NOW() "c", SYSDATE() "d";
+---------------------+---------------------+---+---------------------+---------------------+
| a                   | b                   | _ | c                   | d                   |
+---------------------+---------------------+---+---------------------+---------------------+
| 2015-11-04 11:18:55 | 2015-11-04 11:18:55 | 0 | 2015-11-04 11:18:55 | 2015-11-04 11:18:57 |
+---------------------+---------------------+---+---------------------+---------------------+
1 row in set (2.01 sec)

As you can see, SYSDATE() increases during the query, while NOW() remains constant. It turns out that UNIX_TIMESTAMP() is also evaluated once per query:

mysql> select UNIX_TIMESTAMP(), SLEEP(2), UNIX_TIMESTAMP();
+------------------+----------+------------------+
| UNIX_TIMESTAMP() | SLEEP(2) | UNIX_TIMESTAMP() |
+------------------+----------+------------------+
|       1446635945 |        0 |       1446635945 |
+------------------+----------+------------------+
1 row in set (2.00 sec)
r3mainer
  • 23,981
  • 3
  • 51
  • 88
  • Damn you ;) I was playing around with this before I had a look in the documentation, but then somehow didn't think of demonstrating with `sysdate()`. Good job, upvote. – fancyPants Nov 04 '15 at 11:37
  • Awesome and simple example, thanks! Just tried select UNIX_TIMESTAMP() "a", SYSDATE() "b", SLEEP(2) "_", UNIX_TIMESTAMP() "c", SYSDATE() "d"; And timestamp didn't changed! – nikitasius Nov 04 '15 at 11:42
1

I guess it's the same as for the NOW() function:

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

Unfortunately there's no specific documentation for other functions regarding this case. If you want to make sure, to always have the same value, just NOW() with UNIX_TIMESTAMP(), like

 SELECT UNIX_TIMESTAMP(NOW());
fancyPants
  • 50,732
  • 33
  • 89
  • 96