CODEWITHSUNDEEP
mysqlsqldatedatetime
ZAX
ZAX

Reputation: 1008

Search MySQL Database Based Soley on Date rather than Date and Time

I have a table which holds the datetime information of creation of that row. However I am trying to count the number of creations per day, in a given time frame.

select count(*), DateCreated FROM Stories WHERE DateCreated BETWEEN '2013-03-28' AND '2014-01-07' group by DateCreated;

However, because I have them grouped by DateCreated, I get a count of 1 for everything, despite knowing that multiple stories were created on the same day; the time portion of the datetime field makes each one unique enough to group by though.

Is there a way I might modify my query to ignore the time portion of the datetime and use soley the date for distinction in the group by?

Upvotes: 0

Views: 51

Answers (2)

Damith
Damith

Reputation: 63065

Just cast DateCreated to DATE like below 

SELECT
     count(*), DATE(DateCreated) 
FROM 
     Stories 
WHERE 
     DateCreated BETWEEN '2013-03-28' AND '2014-01-07' 
GROUP BY
    DATE(DateCreated);

Upvotes: 0

Mitch Wheat
Mitch Wheat

Reputation: 300549

SELECT
    NumPerDay   = count(*), 
    DateCreated = cast(DateCreated as Date)
FROM 
    Stories 
WHERE 
    cast(DateCreated as Date) BETWEEN '2013-03-28' AND '2014-01-07' 
GROUP BY 
    cast(DateCreated as Date)

Or 

SELECT
    NumPerDay   = count(*), 
    DateCreated = DATE(DateCreated)
FROM 
    Stories 
WHERE 
    Date(DateCreated) BETWEEN '2013-03-28' AND '2014-01-07' 
GROUP BY 
    DATE(DateCreated)

Or if casting Datecolumn in where clause prevents use of an available index:

SELECT
    NumPerDay   = count(*), 
    DateCreated = DATE(DateCreated)
FROM 
    Stories 
WHERE 
    DateCreated BETWEEN '2013-03-28 00:00:00' AND '2014-01-07 23:59:59.999' 
GROUP BY 
    DATE(DateCreated)

Upvotes: 2

Related Questions