Select data and date range window in mysql with every 7 day interval from now

Question

I have a table called symposium table, which is like this.

|----------------------------------------------------------------|
|      id      |   symposium_date  |   topic     |    price      |
|----------------------------------------------------------------|
|       1      |    2019-09-10     |   Piano     |   30.00       |
|       2      |    2019-09-09     |   Flute     |   20.00       |
|       3      |    2019-09-02     |   Piano     |   30.00       |
|       4      |    2019-08-26     |   Flute     |   20.00       |
|       5      |    2019-08-27     |   Flute     |   20.00       |
-----------------------------------------------------------------|

Say when I am running a query, the NOW() returns me the current datetime ie. 2019-09-13 05:21:50.

I want a query which will return me the start and end date of each seven day interval tracing backward from NOW(), within which each record falls. For better understanding, check this return set I want

|----------------------------------------------------------------|
| id  |   symposium_date  |   from-interval |    to-interval     |
|----------------------------------------------------------------|
| 1   |    2019-09-10     |   2019-09-07    |   2019-09-13       |
| 2   |    2019-09-09     |   2019-09-07    |   2019-09-13       |
| 3   |    2019-09-02     |   2019-08-31    |   2019-09-06       |
| 4   |    2019-08-26     |   2019-08-24    |   2019-09-30       |
| 5   |    2019-08-27     |   2019-08-24    |   2019-09-30       |
| ---------------------------------------------------------------|

How can I achieve this?

My current query is like his:-

SELECT id, symposium_date from FROM symposia 
WHERE (
         symposium_date >= ( NOW() + INTERVAL -7 DAY ) 
           AND symposium_date <= NOW() 
      ) 
      OR  symposium_date < (NOW() + INTERVAL -7 DAY)

But how can I fetch the interval range?

Answer 1

My previous answer is generally suited for the cases when you need all the intervals (irrespective of a date lying in those intervals or not). However, in this particular case of yours, as (thankfully) pointed out by @Strawberry, you don't really need dynamic number of intervals. So, taking hints from answers by @Jitendra, we can employ some mathematics to get the dynamic ranges.

Logic: WeekDay() function returns the weekday index for a given date (0 = Monday, 1 = Tuesday ..). In you case, boundaries of a "week (7-day interval)" are defined based on the day of Current_Date.

So, if the Current_Date is Friday, we need to determine how many days we need to shift from a symposium_date to get weekday index corresponding to the next Friday (basically WeekDay(Current_Date) value). This shift will be simply WeekDay(Current_Date) - WeekDay(symposium_date) and will give us to_interval date.

Now, getting from_interval date is easy; simply subtract 6 days (since we have 7-day interval) from the expression corresponding to to_interval date.

Schema (MySQL v5.7)

CREATE TABLE symposia
    (`id` int, `symposium_date` date, `topic` varchar(5), `price` decimal(11,2))
;

INSERT INTO symposia
    (`id`, `symposium_date`, `topic`, `price`)
VALUES
    (1, '2019-09-10', 'Piano', 30.00),
    (2, '2019-09-09', 'Flute', 20.00),
    (3, '2019-09-02', 'Piano', 30.00),
    (4, '2019-08-26', 'Flute', 20.00),
    (5, '2019-08-27', 'Flute', 20.00)
;

---

Query #1

SELECT 
  id, 
  symposium_date, 
  symposium_date + INTERVAL (WEEKDAY(CURRENT_DATE) - WEEKDAY(symposium_date) - 6) DAY AS from_interval, 
  symposium_date + INTERVAL (WEEKDAY(CURRENT_DATE) - WEEKDAY(symposium_date)) DAY AS to_interval
FROM symposia;

| id  | symposium_date | from_interval | to_interval |
| --- | -------------- | ------------- | ----------- |
| 1   | 2019-09-10     | 2019-09-07    | 2019-09-13  |
| 2   | 2019-09-09     | 2019-09-07    | 2019-09-13  |
| 3   | 2019-09-02     | 2019-08-31    | 2019-09-06  |
| 4   | 2019-08-26     | 2019-08-24    | 2019-08-30  |
| 5   | 2019-08-27     | 2019-08-24    | 2019-08-30  |

---

View on DB Fiddle

Answer 2

Note: Following approach is suitable when there is a need of all the intervals. Eg: You need to get all the week range, irrespective of a symposium_date existing in the range or not. If you don't need all the intervals, then use this answer: https://stackoverflow.com/a/57929123/2469308

---

Now, you require dynamic "start date" and "end date" for a "week" (7 day interval), depending on the non-deterministic NOW() function.

One way can be to utilize a number generator table here. You can either define a permanent table in your database storing integers ranging from 0 to 1000 (let's say). Or, you can use a Derived Table (materialize a table for this query). For brevity, I am considering upto 3 intervals; however, you can also increase the numbers in the below query:

(SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2) AS ngen

Now, we can create 7-day intervals starting from CURRENT_DATE() using some mathematics:

SELECT CURRENT_DATE() - INTERVAL (((ngen.digit+1)*7)-1) day AS from_interval,
       CURRENT_DATE() - INTERVAL ((ngen.digit)*7) day       AS to_interval
FROM   (SELECT 0 AS digit
        UNION ALL
        SELECT 1
        UNION ALL
        SELECT 2) ngen;

| from_interval | to_interval |
| ------------- | ----------- |
| 2019-09-07    | 2019-09-13  |
| 2019-08-31    | 2019-09-06  |
| 2019-08-24    | 2019-08-30  |

Now, we can simply use the above result-set as a Derived Table and JOIN back to symposia table matching the symposium_date lying BETWEEN the specific pair of from_interval and to_interval:

Schema (MySQL v5.7)

CREATE TABLE symposia
    (`id` int, `symposium_date` date, `topic` varchar(5), `price` decimal(11,2))
;

INSERT INTO symposia
    (`id`, `symposium_date`, `topic`, `price`)
VALUES
    (1, '2019-09-10', 'Piano', 30.00),
    (2, '2019-09-09', 'Flute', 20.00),
    (3, '2019-09-02', 'Piano', 30.00),
    (4, '2019-08-26', 'Flute', 20.00),
    (5, '2019-08-27', 'Flute', 20.00)
;

---

Query

SELECT s.id,
       s.symposium_date,
       weeks.from_interval,
       weeks.to_interval
FROM   symposia s
       JOIN (SELECT CURRENT_DATE() - INTERVAL (((ngen.digit+1)*7)-1) day AS
                    from_interval,
                    CURRENT_DATE() - INTERVAL ((ngen.digit)*7) day       AS
                    to_interval
             FROM   (SELECT 0 AS digit
                     UNION ALL
                     SELECT 1
                     UNION ALL
                     SELECT 2) ngen) AS weeks
         ON s.symposium_date BETWEEN weeks.from_interval AND weeks.to_interval;

Result

| id  | symposium_date | from_interval | to_interval |
| --- | -------------- | ------------- | ----------- |
| 1   | 2019-09-10     | 2019-09-07    | 2019-09-13  |
| 2   | 2019-09-09     | 2019-09-07    | 2019-09-13  |
| 3   | 2019-09-02     | 2019-08-31    | 2019-09-06  |
| 4   | 2019-08-26     | 2019-08-24    | 2019-08-30  |
| 5   | 2019-08-27     | 2019-08-24    | 2019-08-30  |

---

View on DB Fiddle

Answer 3

UPDATED: If Week range is dynamic based on a date:

Day of given date would be considered as last day of week in this.

SET @c_date:= '2019-09-13';

SELECT 
    s.id,
   s.symposium_date,
   @st_date:= s.symposium_date + INTERVAL DAYOFWEEK(@c_date) - DAYOFWEEK(s.symposium_date) + 1 - IF(DAYOFWEEK(@c_date) >= DAYOFWEEK(s.symposium_date), 7 , 0) DAY from_interval,
    @st_date + INTERVAL 6 DAY to_interval
FROM symposia s;

See the Fiddle for dynamic range.

OLD: If you already know what would be the start and end day of a week.

MySQL provides two different functions to retrieve the first day of a week.

DAYOFWEEK: Returns the weekday index for date (1 = Sunday, 2 = Monday, …, 7 = Saturday). These index values correspond to the ODBC standard.

WEEKDAY: Returns the weekday index for date (0 = Monday, 1 = Tuesday, … 6 = Sunday).

If you consider Saturday as first day and Friday as last day(as shown in your output).

SELECT 
    s.symposium_date,
    SUBDATE(s.symposium_date, DAYOFWEEK(s.symposium_date)) start_date, -- Monday would be first day
    SUBDATE(s.symposium_date, DAYOFWEEK(s.symposium_date) - 6) end_date -- Add 6 day to find end date, sunday would be last day of week
FROM symposia s;

If you consider Monday as the first day of week:

SELECT 
    s.symposium_date,
    SUBDATE(s.symposium_date, WEEKDAY(s.symposium_date)) start_date, -- Monday would be first day
    SUBDATE(s.symposium_date, WEEKDAY(s.symposium_date) - 6) end_date -- Add 6 day to find end date, sunday would be last day of week
FROM symposia s;

You can do alternate, to find Sunday as first_day and Saturday as last.

SELECT 
    s.symposium_date,
    SUBDATE(s.symposium_date, WEEKDAY(s.symposium_date) + 1) start_date, -- Sunday would be first day
    SUBDATE(s.symposium_date, WEEKDAY(s.symposium_date) - 5) end_date -- Add 5 day to find end date, Saturday would be last day of week
FROM symposia s;

Answer 4

Just to supplement Jtrendra's answer, consider the following - it may not be 100% correct, but hopefully it demonstrates a principle of determining dates relative to other dates.

Let's say that today is a Friday (actually Friday 13th, 2019), and so we want to know on what date Friday occurred in, say, the first week of January. This is equivalent to the to_interval value in the OP's example.

I'm using @variables for this example, but you can easily substitute CURDATE() or some other dynamic value...

SET @target_date = '2019-01-01';
SET @current_date = '2019-09-13';

SELECT STR_TO_DATE(CONCAT_WS(' '
                            , YEAR(@target_date)
                            , WEEK(@target_date,WEEKDAY(@current_date)) -- WEEKDAY might need tweaking
                            , DATE_FORMAT(@current_date,'%a')
                            ),'%Y %u %a') x;
+------------+
| x          |
+------------+
| 2019-01-04 |
+------------+