At 17:50 -0500 5/16/04, Paul DuBois wrote:
At 0:38 +0200 5/17/04, John Fawcett wrote:
From: "Paul DuBois"
You're right. You'd have to apply YEAR() to
FROM_UNIXTIME(UNIX_TIMESTAMP(arg)).
and you can avoid YEAR() altogether by using a
format string. in FROM_UNIXTIME()
Right again. :-)
I was curious about which might be faster, and got these results on a
2.8GHz Pentium IV with MySQL 4.1.2 on Linux:
mysql> select benchmark(10000000,from_unixtime(unix_timestamp(),'%Y'));
+----------------------------------------------------------+
| benchmark(10000000,from_unixtime(unix_timestamp(),'%Y')) |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (11.50 sec)
mysql> select benchmark(10000000,year(from_unixtime(unix_timestamp())));
+-----------------------------------------------------------+
| benchmark(10000000,year(from_unixtime(unix_timestamp()))) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
1 row in set (8.72 sec)
On a 900MHz PowerPC G3 with MySQL 4.0.19 on Mac OS X:
mysql> select benchmark(10000000,from_unixtime(unix_timestamp(),'%Y'));
+----------------------------------------------------------+
| benchmark(10000000,from_unixtime(unix_timestamp(),'%Y')) |
+----------------------------------------------------------+
| 0 |
+----------------------------------------------------------+
1 row in set (55.17 sec)
mysql> select benchmark(10000000,year(from_unixtime(unix_timestamp())));
+-----------------------------------------------------------+
| benchmark(10000000,year(from_unixtime(unix_timestamp()))) |
+-----------------------------------------------------------+
| 0 |
+-----------------------------------------------------------+
1 row in set (36.39 sec)
Not a huge difference, I guess. But I suppose if a query that
uses one or the other of these expressions processes a large number
of rows, it might pay to run some comparative testing.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
