20

Hopefully, it's an easy solution, but I am trying to filter the data for the following:-

  • Today
  • Yesterday
  • This Week
  • This Month
  • In between two dates.

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?

René Wolferink
  • 3,558
  • 2
  • 29
  • 43
Steve
  • 2,546
  • 8
  • 49
  • 94

1 Answers1

55

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:

  • Today: WHERE timestamp >= CURDATE()
  • Yesterday: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND timestamp < CURDATE()
  • This month: WHERE timestamp >= DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY)
  • Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June): 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:

  • Today: WHERE timestamp >= UNIX_TIMESTAMP(CURDATE())
  • Yesterday: WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) AND timestamp < UNIX_TIMESTAMP(CURDATE())
  • This month: WHERE timestamp >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL DAYOFMONTH(CURDATE())-1 DAY))
  • Between the two dates 3 June 2013 and 7 June 2013 (note how the end date is specified as 8 June, not 7 June): WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')
Ed Gibbs
  • 25,924
  • 4
  • 46
  • 69
  • Thanks a lot, Ed. It seems to be working. Only one gripe; the today seems to be showing all the records. When I do timestamp = CURDATE() instead, it doesn't show anything. any idea? – Steve Jun 19 '13 at 18:50
  • Hmm, it works for me with my small test table. What happens when you try `WHERE DATE(timestamp) = CURDATE()`? I've avoided using `DATE(timestamp)` because if you have an index on the `timestamp` column (and you probably should if you'll do a lot of these queries), using the `DATE` function on the column will kill any chance of optimization. – Ed Gibbs Jun 19 '13 at 18:55
  • Hi Ed, It seems like Today and Yesterday both have problems. Today shows all, Yesterday shows none. WHERE DATE(timestamp) = CURDATE() shows none too. Month works perfectly. I think it doesn't like timestamp >= CURDATE() or timestamp < CURDATE() – Steve Jun 19 '13 at 19:10
  • Which data type is the `timestamp` column? I assumed the [`TIMESTAMP`](http://dev.mysql.com/doc/refman/5.1/en/datetime.html) data type and tested with that. – Ed Gibbs Jun 19 '13 at 19:18
  • It's int :-( ... Is that a problem? – Steve Jun 19 '13 at 19:21
  • Oops; bad assumption on my part :) An `INT` definitely won't work with my answer. Is it a UNIX timestamp value? – Ed Gibbs Jun 19 '13 at 19:31
  • Yes, it is UNIX timestamp. Thanks a lot for your assistance – Steve Jun 19 '13 at 19:33
  • No problem! In that case, I think you can just wrap all the `DATETIME` values in the answer in the MySQL [`UNIX_TIMESTAMP()`](http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp) function. For example, "Today" would be `WHERE timestamp >= UNIX_TIMESTAMP(CURDATE())` and "Between 3 June and 8 June" would be `WHERE timestamp >= UNIX_TIMESTAMP('2013-06-03') AND timestamp < UNIX_TIMESTAMP('2013-06-08')` and so forth. Does that make sense? More importantly, does it work? If so, I'll update my answer. – Ed Gibbs Jun 19 '13 at 19:37
  • Glad to hear it! I'll add the `UNIX_TIMESTAMP` thing to the answer in case it's useful to anyone else - I think it will be because I see quite a few questions about dealing with UNIX timestamp values. – Ed Gibbs Jun 19 '13 at 19:48