在SQL(MySQL)中根据今天的日期返回查询结果 [英] Return results of query based on todays date in SQL (MySQL)

问题描述

我有一个需要帮助的查询，但是我被困住了.

I have a query which I got help with but I am stuck on another bit.

我的代码是

SELECT a.name, COUNT(*) AS num FROM table2 b 
INNER JOIN table1 a 
ON b.status_id=a.id 
GROUP BY status_id

我现在想做的是仅显示在当前日期输入的结果?日期列在表2中.日期列的格式是日期和时间(例如1341241153)，它由CRM通过这种方式自动设置.我不确定这是什么格式！

What I would like to do now is only show the results if they have been entered on the current date? The date column is in table2. The format for the date column is date and time (eg 1341241153) which is automatically set by the CRM in this way. I am not sure what format this is in!

我只需要检查日期是否与当前日期匹配.我希望这很清楚.

I only need to check if the date matches the current day. I hope that is clear.

这是一个MySQL数据库.

This is a MySQL database.

将不胜感激地收到任何帮助！

Any help will be gratefully received!

推荐答案

您应在日期列上使用from_unixtime()函数，该函数应包含1341241153之类的值.
因为这些值似乎以unix时间戳格式存储.

You should use from_unixtime() function on date column that holds values like 1341241153.
Because these values seem stored in unix timestamp format.

示例:

mysql> select
    ->     from_unixtime( 1341241153 ) as 'my_datetime_1341241153',
    ->     date( from_unixtime( 1341241153 ) ) as 'my_date_1341241153',
    ->     curdate(),
    ->     curdate() > date( from_unixtime( 1341241153 ) ) 'is_today_later?',
    ->     curdate() = date( from_unixtime( 1341241153 ) ) 'is_today_equal?',
    ->     curdate() < date( from_unixtime( 1341241153 ) ) 'is_today_before?'
    -> from
    ->     dual
    -> \G
*************************** 1. row ***************************
my_datetime_1341241153: 2012-07-02 20:29:13
    my_date_1341241153: 2012-07-02
             curdate(): 2012-07-15
       is_today_later?: 1
       is_today_equal?: 0
      is_today_before?: 0
1 row in set (0.00 sec)

---

您的查询应为:

---

Your query should be:

SELECT a.name, COUNT(*) AS num FROM table2 b 
INNER JOIN table1 a 
ON ( b.status_id=a.id and curdate() = date( from_unixtime( b.crm_date_time_column ) ) )
GROUP BY status_id

这篇关于在SQL(MySQL)中根据今天的日期返回查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！