How to Use SQL to Generate a Continuous Sequence of Dates

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 怎么用SQL查询生成一个连续的日期

| username: 望海崖2084

[TiDB Usage Environment] Production Environment
[TiDB Version] v5.4.1
[Encountered Problem: Problem Phenomenon and Impact]

  • As mentioned, given the start date and end date, obtain the continuous dates, corresponding weeks, and corresponding weekdays.
  • In TiDB, mysql.help_topic is empty when I query locally, so it can’t be used.
2
| username: dba远航 | Original post link

This should be achievable in any database through SQL or stored procedures, but it will require some brainpower.

3
| username: zhanggame1 | Original post link 
WITH RECURSIVE dates(d) AS (
  SELECT DATE('2022-03-01') d
  UNION ALL 
  SELECT d + INTERVAL 1 DAY FROM dates WHERE d < DATE('2022-03-31')
)
SELECT * FROM dates;
4
| username: forever | Original post link

set @rank=0;
select date_add(‘2023-09-02’,interval @rank:=@rank+1 day) from information_schema.tables a where @rank<datediff(‘2024-01-02’,‘2023-09-02’);
or
select date_add(‘2023-09-02’,interval @rank:=@rank+1 day) from information_schema.tables a,(select @rank:=0) b where @rank<datediff(‘2024-01-02’,‘2023-09-02’);
Although not very elegant, it is also a solution.

5
| username: 江湖故人 | Original post link

Similar to @forever, you can also do this:

select d, week(date(d)) wd, weekday(date(d))+1 w 
  from (select date_add('2023-01-01', interval (row_number() over())-1 day) d  
    from metrics_schema.tikv_scheduler_scan_details) dt 
where d <= date '2023-07-31';
6
| username: 江湖故人 | Original post link

An error will occur when the nesting level exceeds 1000. You can increase the session’s cte_max_recursion_depth.

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.