Menu
Hopefully, it's an easy solution, but I am trying to filter the data for the following:-
The date I get from the database is basically a timestamp.
This is what I tried:-
Today
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 1 DAY)
Yesterday
SELECT n.title, COUNT(*) AS times FROM node_view_count WHERE timestamp > DATE_SUB(NOW(), INTERVAL 7 DAYS)
...
It's not really working.
Any idea?
731
In MySQL, use the DATE() function to retrieve the date from a datetime or timestamp value. This function takes only one argument – either an expression which returns a date/datetime/ timestamp value or the name of a timestamp/datetime column. (In our example, we use a column of the timestamp data type.)
How Do You Group Data by Week in SQL Server? SQL Server provides a function called DATEPART() , which returns a specified part (year, quarter, month, week, hour, minute, etc.) of a specified date. ORDER BY DATEPART(week, RegistrationDate);
If you're selecting by date only, base your calculations on CURDATE (which returns date only) rather than NOW (which returns date and time). These examples will catch all times within the day ranges:
WHERE timestamp >= CURDATE()
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE()
WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
WHERE timestamp >= '2013-06-03' AND timestamp < '2013-06-08'
The "this week" depends on which day you start your week; I'll leave that to you. You can use the DAYOFWEEK function to tweak CURDATE() to the proper ranges.
Addendum: OP's column type was INTEGER, storing a UNIX timestamp, and my answer assumed the column type was TIMESTAMP. Here's how to do all the same things with a UNIX timestamp value and still maintain optimization if the column is indexed (as the answers above will do if the TIMESTAMP column is indexed)...
Basically, the solution is to just wrap the beginning and/or ending dates in the UNIX_TIMESTAMP function:
WHERE timestamp >= UNIX_TIMESTAMP(CURDATE())
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE())
WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY))
WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
