Can I get two different results from UNIX_TIMESTAMP in the same query?

Question

This seems obvious, but I'm just not sure about the correct answer.

If I use the INSERT / UPDATE command in a single mysql query, can I get two different results from UNIX_TIMESTAMP ? That is, does the time change during a single request?

Example:

 UPDATE my_table SET time1 = UNIX_TIMESTAMP(), ... ... time2 = UNIX_TIMESTAMP(), ... 

Is it possible that time2 will be greater than time1 ?

(for those who ask what's good to set two columns to the same value - I use one for the added time and time so that I can only sort by one column)

If possible, provide some background information for your answer. Thanks!

Answer 1

MySQL's time and date functions return the time / start date of the statement, so if you run:

 CREATE TABLE t ( x INT ); INSERT INTO t SELECT UNIX_TIMESTAMP() FROM (10M rows table) -- takes several seconds SELECT DISTINCT x FROM t; 

DISTINCT returns a single value corresponding to the time when the INSERT began to execute.

Answer 2

I do not know the answer to your question. However, believing that your motivation is to get consistent timestamps when writing to the table, why not use the following approach: create a stored procedure. Inside the procedure, assign the variable using UNIX_TIMESTAMP() and execute the UPDATE or INSERT query using the variable, not further calls to UNIX_TIMESTAMP() . This way you are guaranteed the right behavior.

Example:

 CREATE PROCEDURE "My_Insert_Procedure" () LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY DEFINER BEGIN DECLARE my_time DATETIME; SET my_time = UNIX_TIMESTAMP(); UPDATE my_table SET time1 = my_time, ... ... time2 = my_time, ... END CALL My_Insert_Procedure(); 

Answer 3

In mysql, unix_timestamp () is similar to now () - it returns the time when the instruction started executing and differs from sysdate (), which returns the time when the function itself is executed.

 CREATE TABLE test ( date datetime, tstamp int(11) ); INSERT INTO test VALUES( 0,0 ), ( 0,0 ), ( 0,0 ); UPDATE test SET date=sysdate(), tstamp=unix_timestamp() WHERE !sleep(2); +---------------------+------------+ | date | tstamp | +---------------------+------------+ | 2011-05-20 22:39:58 | 1305923996 | | 2011-05-20 22:40:00 | 1305923996 | | 2011-05-20 22:40:02 | 1305923996 | +---------------------+------------+