在 mysql 5.0 中使用 REGEXP 比较文件
我想要一个嵌套的 mysql 请求来计算每天的操作，如下所示：
SELECT `timestamp` AS `date`,
(SELECT COUNT('id')
FROM `actions` WHERE `timestamp` = `date`) AS `action_num`
FROM `actions`;
如果不是这样的话，那会很好，这样我就需要计算每一天，而我得到的只是每个时间戳。如果只是约会的话就可以了。所以我想如果我可以从字符串中取出日期并进行比较，这可能会起作用：
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`;
但它不起作用。
关于如何直接在 MySQL 中比较两个时间戳字段的日期有什么想法吗？
谢谢
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
非常简单，内部查询应该是：
参见： http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
Very easy, the inner query should be:
See: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date
您可以使用
DATEDIFF()函数来比较MySQL中的日期，TIMESTAMPDIFF()用于比较时间戳，DATE()用于从时间戳获取日期信息。更多日期和时间函数和示例可在 MySQL 中找到参考手册页。
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.