MySQL - Obtain the records made during the current week

0
votes

I'm making a panel where I want to show the number of records made per day, week, month and year.

When a new record is made, the date with the format d/m/Y is saved in the database in a column of the table.

For example to obtain how many records have been made in a day I use:

SELECT COUNT(*) FROM {$wpdb->prefix}emails WHERE registration_date = '$today'

What I want to achieve is to know how many records I have obtained in the week in which we are knowing that the week starts on Monday and ends on Sunday.

The truth is that I have no great idea to make queries and any help is welcome.

Thanks in advance.

2
phpmysqlwordpress
How about something like stackoverflow.com/questions/11461420/…? – Tripp Kinetics

2 Answers

0
votes

I did not get your goal.

Here is query to get count for one week of some date:

SELECT COUNT(*) 
FROM {$wpdb->prefix}emails 
WHERE registration_date BETWEEN 
      DATE_ADD($yourDate, INTERVAL - WEEKDAY($yourDate) DAY),
      DATE_ADD($yourDate, INTERVAL (7 - WEEKDAY($yourDate)) DAY)
0
votes

You need to compare dates against a range, for example, to get dates for the last week:

SELECT * FROM yourtable WHERE (thedate >= DATE_SUB(INTERVAL 1 WEEK)) AND
(thedate < DATE());

MySQL has several date functions for that.