How to get unix timestamp interval between two datetime values most efficiently in MySQL?

Question

This way surely works,but it calls UNIX_TIMESTAMP 2 times:

mysql> select UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP('2009-09-23 22:07:42');
+---------------------------------------------------------------+
| UNIX_TIMESTAMP(now()) - UNIX_TIMESTAMP('2009-09-23 22:07:42') |
+---------------------------------------------------------------+
|                                                           639 |
+---------------------------------------------------------------+
1 row in set (0.05 sec)

Answer 1

Shouldn't

TIMESTAMPDIFF(SECOND, now(), '2009-09-23 22:07:42')

do the same with just one function call (not counting now())? (No access to MySQL right now and MSSQL works a little different, so can't test).

Basically a UNIX timestamp is the number of seconds since a weird epoch, so a difference is just a difference in seconds. Also, this function is only available in MySQL 5 and later.

But in general, worry about performance when you have a problem, write readable code until then.

Answer 2

SELECT  TIME_TO_SEC(TIMEDIFF(NOW(), '2009-09-23 22:07:42'))