I have a log table that records certain transactions on one of my sites. I'm using a timestamp field to mark the date/time of each transaction.
I'm trying to run a query that will display the transactions by date using the following SQL: SELECT COUNT(log_id) AS total, WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d'), 3) AS week, MONTHNAME(FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y-%m-%d')) AS month, FROM_UNIXTIME(UNIX_TIMESTAMP(cdate), '%Y') AS year FROM log However, when I run this query I get very strange results for the week and month. They don't seem to match up. For example: +-------+------+-----------+------+ | total | week | month | year | +-------+------+-----------+------+ | 16869 | 49 | December | 2003 | | 16782 | 50 | December | 2003 | | 14668 | 51 | December | 2003 | | 14121 | 52 | December | 2003 | | 15453 | 2 | January | 2004 | | 20332 | 3 | January | 2004 | | 41788 | 4 | January | 2004 | | 29223 | 5 | January | 2004 | | 23143 | 6 | February | 2004 | | 23463 | 7 | February | 2004 | | 4463 | 53 | December | 2004 | +-------+------+-----------+------+ I guess I'm wondering if there is a known bug with the way weeks are calculated as they don't seem to match up with the months. In the results above - what happened to week 1 in January 2004? Any ideas would be greatly appreciated. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
