MySQL: How to get "this week's date" using the current date?

Question

Hi,

I have this SQL query about getting the 5 events today:

SELECT n.nid, n.type, n.title, nr.body, nr.teaser, FROM_UNIXTIME(e.event_start) start_date, FROM_UNIXTIME(e.event_end) end_date
FROM node n
LEFT JOIN event e ON n.nid = e.nid
LEFT JOIN node_revisions nr ON nr.nid = e.nid
WHERE n.`type` = 'event'
AND NOW() BETWEEN FROM_UNIXTIME(e.event_start) AND FROM_UNIXTIME(e.event_end)
ORDER BY n.`created` DESC
LIMIT 5

Then I need to get the "this week's event" using "a week that includes "today" and starts on a Sunday".

How can I do that in MySQL?

Any help would greatly be appreciated. Thanks in advance :)

Cheers, Mark

Answer 1

You need to define "this week" better -- do you mean a 7-days sliding window centered on today, or a week (the one that includes "today") starting e.g. on a Sunday? That's entirely dependent on the semantics of "this week" and it's impossible for us to decide what you meant by said ambiguous expression. Of the two approaches you mention, one or the other (or a variant thereon) will be appropriate depending on your meaning.

Edit: the OP has clarified in a comment that he means "a week that includes "today" and starts on a Sunday" -- and I deduce from his use of FROM_UNIXTIME that the specific SQL dialect he's targeting is MySQL. Then, WEEK(somedate, 0) is the MySQL function that should give him exactly what he wants, see mysql's docs.

Specifically,

AND WEEK(CURDATE, 0) BETWEEN WEEK(FROM_UNIXTIME(e.event_start), 0)
                         AND WEEK(FROM_UNIXTIME(e.event_end), 0)

should be the WHERE clause the OP is looking for.

Answer 2

I'm not sure if this is for SQL Server or MySQL, but in MySQL you could get the current weekday of today and then use date_add to subtract that many days from the current date (start date) then using start date, use date_add again to add 7 days (end date).

Hopefully that helps, let me know if you need help with the syntax.

Answer 3

Based on the table/column names, it appears you're working with Drupal. Have you considered using a View to achieve your goal? I can't tell from the context whether this is part of a module you're writing, in which case keep plugging away, or whether you just want a list of events to display in a block, in which case a View should be able to do all this for you without messing around with PHP/SQL.

Answer 4

I don't know if you have that option, but for performance reasons it could be better to do the date calculations in your program code. If you use a function on a column in a WHERE clause, MySQL cannot use indexes. A simple example: http://netfactory.dk/2004/12/13/mysql-date-functions-and-indexes/ Most languages should have decent functions/libraries for date/time manipulation.