Can I get two different results from UNIX_TIMESTAMP in one query?

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

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

Example:

UPDATE my开发者_JS百科_table SET
time1 = UNIX_TIMESTAMP(),
...
...
time2 = UNIX_TIMESTAMP(),
...

Is it possible that time2 will be larger than time1?

(for anyone asking what good it is to set two columns to the same value - I'm using one for the time added and time updated so that I can sort just by one column)

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

MySQL time & date functions return the time/date of the beginning of the statement, so if you do :

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 one value, which correspond to the time when the INSERT began executing.

In mysql unix_timestamp() is similar to now() - it returns time when the statement began to execute and is different to sysdate() which returns 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 |
+---------------------+------------+

I do not know the answer to your question. However, assuming your motivation is to get consistent timestamps when writing to the table, why not take the following approach: create a stored procedure. Inside the procedure, assign to a variable using UNIX_TIMESTAMP(), and execute your UPDATE or INSERT query using the variable, rather than further calls to UNIX_TIMESTAMP(). That way you are guaranteed the correct behaviour.

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();