CODEWITHSUNDEEP
mysqlmysql-logic
Derek Adair
Derek Adair

Reputation: 21925

Is it possible to group rows by a day stored within a timestamp?

I'm not sure if this is even within the scope of MySQL to be honest or if some php is necessary here to parse the data. But if it is... some kind of stored procedure is likely necessary.

I have a table that stores rows with a timestamp and an amount.

My query is dynamic and will be searching based on a user-provided date range. I would like to retrieve the SUM() of the amounts for each day in a table that are between the date range. including a 0 if there are no entries for a given day

Something to the effect of...

SELECT 
   CASE
     WHEN //there are entries present at a given date
       THEN SUM(amount)
     ELSE 0
   END AS amountTotal,
   //somehow select the day
   FROM  thisTableName T
   WHERE T.timeStamp BETWEEN '$start' AND '$end'
   GROUP BY //however I select the day

This is a two parter...
is there a way to select a section of a returned column? Like some kind of regex within mysql?
Is there a way to return the 0's for dates with no rows?

Upvotes: 3

Views: 129

Answers (1)

St.Woland
St.Woland

Reputation: 5417

select * from thisTableName group by date(created_at);

In your case, it would be more like

SELECT id, count(id) as amountTotal
FROM thisTableName
WHERE timeStamp BETWEEN '$start' AND '$end'
GROUP BY DATE(timeStamp);

Your question is a duplicate so far: link.

Upvotes: 2

Related Questions