CODEWITHSUNDEEP
mysqlsqlwhere-clause
5ummer5
5ummer5

Reputation: 153

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.

The code I have is

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

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.

This is a MySQL database.

Any help will be gratefully received!

Upvotes: 1

Views: 1292

Answers (3)

Sashi Kant
Sashi Kant

Reputation: 13455

Try this::

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

where DATE(column_date) = DATE(now())  
GROUP BY status_id

Upvotes: 2

Zane Bien
Zane Bien

Reputation: 23125

Use this solution:

SELECT     a.name, COUNT(*) AS num
FROM       table2 b
INNER JOIN table1 a ON b.status_id = a.id
WHERE      b.datecol >= UNIX_TIMESTAMP(CURDATE()) AND
           b.datecol < UNIX_TIMESTAMP(CURDATE() + INTERVAL 1 DAY)
GROUP BY   b.status_id

This will avoid wrapping your date column inside a function which would make the query non-sargable (i.e. not able to use indexes). By keeping the comparison on the bare date column, MySQL will still able to utilize an index(if you have it set up on the date column), and it should be quite efficient.

You could simplify it even further if you ABSOLUTELY KNOW that the date entered can never be sometime in the future (i.e. tomorrow or the next day):

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

The second conditional check is removed as it doesn't need to check if it's in a future day.

Upvotes: 3

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

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

Example: 

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

Upvotes: 2

Related Questions