Compare fileds with REGEXP in mysql 5.0

I would like to so a nested mysql request that is counting actions for each day like this:

SELECT `timestamp` AS `date`, 
  (SELECT COUNT('id') 
   FROM `actions` WHERE `timestamp` = `date`) AS `action_num`  
FROM `actions`;  

That would work great if it wasn't so that I need to calculate each day and 开发者_运维技巧all I got is each timestamp. If it was only a date it would work. So I thought if I can take out the date from the string and compare it this might work:

SELECT `timestamp` AS `date`, 
  (SELECT COUNT('id') FROM `actions` 
   WHERE `timestamp` REGEXP '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}' = `date`
   REGEXP '[[:digit:]]{4}-[[:digit:]]{2}-[[:digit:]]{2}') AS `action_num`  
FROM `actions`;

But it did not work.

Any ideas on how to compare the dates from two timestamp fields directly in MySQL?

Thanks

---

Very easy, the inner query should be:

SELECT count(id) FROM actions 
WHERE DATE(`timestamp`) = DATE(`date`) as action_num

See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date

---

You can use DATEDIFF() function to compare the dates in MySQL, and TIMESTAMPDIFF() for comparing timestamps , DATE() to get date information from timestamp.
More date and time functions and examples are available in MySQL's reference manual pages.