mysql date show results today/yesterday/week

Question

I am retrieving data from a table and show the total SUM of entries. What I want to do is to show the total SUM of entries made on today's date, yesterday and this month. The table is using the unix timestamp format (e.g. 1351771856 for example).

Currently I am using this line to show todays results:

AND comment_date > UNIX_TIMESTAMP() - 24 * 3600";

but that gives me just the entries for the last 24 hours.

Example: So let's say its Friday, 17:00 PM - it gives me the count from Thursday 17:00 PM to Friday 17:00 PM

What I want is to get the results for

• Thursday 00:00:00 - 23:59:59 (yesterday in this case)
• the results for today (00:00:00 - 23:59:59)
• and last week, results that start on Monday, 00:00:00 until "today" (in this case Friday).

I couldn't find a way in the MySQL documentation to achieve this.

Answer 1

Simply use this:

AND comment_date > date_sub(current_date, interval 1 day)

Answer 2

This mysql code should work for you:

// Today
AND DATE(from_unixtime(comment_date)) = CURRENT_DATE

// Yesterday
AND DATE(from_unixtime(comment_date)) =  DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY)

// This week
AND YEARWEEK(from_unixtime(comment_date), 1) =  YEARWEEK(CURRENT_DATE, 1)

// This month
AND YEAR(from_unixtime(comment_date)) = YEAR(CURRENT_DATE)
AND MONTH(from_unixtime(comment_date)) = MONTH(CURRENT_DATE)

Answer 3

See my answer here, I think it's quite related.

Pull records from orders table for the current week

Consider getting intimate with MySQL's GROUP BY. You will most likely need to know this if you use MySQL.