Time related function execution in SQL queries

Question

Here is an interesting thought.

UPDATE table1
SET field1 = UNIX_TIMESTAMP()
WHERE
    field1 <= UNIX_TIMESTAMP() - 600
AND field2 > UNIX_TIMESTAMP() - 1000

Will the three time stamp may result in different values .. or if there is a chance that MySQL may intelligently evaluate these once and use the result in all three of these ?

if there is a chance that MySQL may intelligently evaluate these once and use the result in all three of these. I've tested it out of curiosity. Called UNIX_TIMESTAMP() 5030 times in a query and it took **206 ** Seconds and the results are all same. — 1000111
– 1000111, Commented Feb 9, 2016 at 15:16

Gordon Linoff · Accepted Answer · 2016-02-09 15:09:29Z

1

Interestingly, this is not properly documented for UNIX_TIMESTAMP(). For NOW(), the documentation is quite clear:

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.

I think UNIX_TIMETAMP() follows the same rules as NOW(), but I'm not 100% sure.

answered Feb 9, 2016 at 15:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

cdaiga · Accepted Answer · 2016-02-09 15:17:23Z

0

I think you don't want to repeat unix_timestamp().

SET @now = NOW();
UPDATE table1
SET field1 = @now
WHERE
   field1 <= @now - 600
   AND field2 > @now - 1000;

answered Feb 9, 2016 at 15:17

cdaiga

4,9374 gold badges25 silver badges44 bronze badges

Collectives™ on Stack Overflow

Time related function execution in SQL queries

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related