MySQL - 如何显示特定月份的所有日期记录？

2 回答

• 4

  如果您知道输入年份和月份，则可以始终设置第一天，例如输入为 Y = 2012 M=02 ，第一天始终为 2012-02-01 ，使用该日期您可以获取最后一天，然后是该范围内的日期 . 有点像

  select a.Date 
  from (
      select last_day('2012-02-01') - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
      from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
      cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
  ) a 
  where a.Date between '2012-02-01' and last_day('2012-02-01') order by a.Date;
  
  +------------+
  | Date       |
  +------------+
  | 2012-02-01 |
  | 2012-02-02 |
  | 2012-02-03 |
  | 2012-02-04 |
  | 2012-02-05 |
  | 2012-02-06 |
  | 2012-02-07 |
  | 2012-02-08 |
  | 2012-02-09 |
  | 2012-02-10 |
  | 2012-02-11 |
  | 2012-02-12 |
  | 2012-02-13 |
  | 2012-02-14 |
  | 2012-02-15 |
  | 2012-02-16 |
  | 2012-02-17 |
  | 2012-02-18 |
  | 2012-02-19 |
  | 2012-02-20 |
  | 2012-02-21 |
  | 2012-02-22 |
  | 2012-02-23 |
  | 2012-02-24 |
  | 2012-02-25 |
  | 2012-02-26 |
  | 2012-02-27 |
  | 2012-02-28 |
  | 2012-02-29 |
  +------------+
  29 rows in set (0.00 sec)
  

  回复于 2024-05-04T04:04:34+08:00
• 0

  下面是两个查询的示例，可用于获取所需的所有记录 . 第一个查询将为您提供当前日期之前5个月的记录，第二个查询将为您提供当月第一天的记录 .

  这些只是示例，因此您可以调整这些查询以显示所需的相关信息 .

  时间戳/日期，可以是日期，也可以是时间戳，具体取决于您选择将日期存储到日期库中的格式

  Example 5 months before current date

  select * from table where timestamp/date >= now() - interval 5 month;
  

  Example 5 months before first day of month

  select * from table where timestamp/date >= last_day(now()) + interval 1 day - interval 5 month;
  

  回复于 2024-05-04T04:04:34+08:00