0

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.

Mikel
  • 27
  • 4
  • 1
    How about something like https://stackoverflow.com/questions/11461420/mysql-round-date-to-the-start-of-the-week-and-month? – Tripp Kinetics Nov 16 '18 at 21:33

2 Answers2

0

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.

Sergio Flores
  • 439
  • 2
  • 8
0

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)
Alex
  • 16,739
  • 1
  • 28
  • 51
  • Based on your idea, I'm getting closer, I think, but it gives me back 0. `SELECT COUNT(*) FROM {$wpdb->prefix}emails WHERE registration_date BETWEEN DATE_ADD('$today', INTERVAL - WEEKDAY('$today') DAY) AND DATE_ADD('$today', INTERVAL (7 - WEEKDAY('$today')) DAY)` – Mikel Nov 16 '18 at 22:28
  • The question is - what is your `$today` value contain? – Alex Nov 16 '18 at 22:55
  • $today is current day in string. `$today = (string)date('d/m/Y')` – Mikel Nov 16 '18 at 23:12
  • https://stackoverflow.com/questions/2215354/php-date-format-when-inserting-into-datetime-in-mysql – Alex Nov 16 '18 at 23:18