Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to load last 7 days into a string(list)

I’m trying to create a program that watches and searches for any logs within given dates.

How can I load the last 7 days in DATE format (YYYY-MM-DD) to a string/string list, or via SQL, or Qt/C++.

Currently, I have this SQL command:

SELECT NOW() + INTERVAL -7 DAY

Which displays (for today, "24. 1. 2024") only one value: "2024-01-17".

What I’d like to get is this:

2024-01-24 n 2024-01-23

2024-01-22

2024-01-21

2024-01-20

2024-01-19

2024-01-18

2024-01-17

I know for sure that I’ve basically told SQL to "pick a date from now seven days back", so it’s wrong as it is, but need to edit it so it’ll display 7 different date values for each day.

Tags:

2

Answers


  1. 0

    You can use a recursive cte to get the result you want:

    WITH RECURSIVE date_ranges AS (
   SELECT CURDATE() as d
   UNION ALL
   SELECT d - INTERVAL 1 DAY
   FROM date_ranges
   WHERE d > CURDATE() - INTERVAL 7 DAY)
SELECT * FROM date_ranges;

    db<>fiddle here

    Login or Signup to reply.
  2. 0

    See example. Generate series from date for MySql

    select date_sub(cast('2024-01-17' as date), interval d day) dt,d
from (values row(0),row(1), row(2), row(3), row(4), row(5), row(6), row(7)
  ) t(d)
    dt d
    2024-01-17 0
    2024-01-16 1
    2024-01-15 2
    2024-01-14 3
    2024-01-13 4
    2024-01-12 5
    2024-01-11 6
    2024-01-10 7
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search