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.
The TIMESTAMP column type is NOT a UNIX timestamp...! It is a 'normal' datetime column, you should use just WEEK(cdate), MONTHNAME(cdate) and so on.
-- Roger
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
