CODEWITHSUNDEEP
mysqlsql-date-functionsmysql-date
ifteapotsareforteawhatarepots4
ifteapotsareforteawhatarepots4

Reputation: 11

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.

Upvotes: 0

Views: 57

Answers (2)

ValNik
ValNik

Reputation: 5673

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

Upvotes: 0

VvdL
VvdL

Reputation: 3210

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

Upvotes: 1

Related Questions