mysql 正在提取日期范围之间的年份和总天数- SQL

mbzjlibv  于 2023-02-15  发布在  Mysql
关注(0)|答案(3)|浏览(131)

我有开始日期和结束日期的数据(比如20th Feb 2018 to 20th Feb 2020),我想找出每年在这个范围内的总天数。
例如:

2018 - x days
, 2019 - 365 days
, 2020 - y days etc.

有没有一种方法可以在SQL中不硬编码年份值?
我试着硬编码这些值,效果很好。但是我想要一个没有硬编码年份值的解决方案

0s7z1bwu

0s7z1bwu1#

我对MySql不够熟悉,不知道这是否会移植,但是这里有一个经过测试和确认的SQL Server解决方案。
The fiddle link is here for your use.
给定开始日期2018年2月20日和2020年2月20日,结果集如下:
| 年份|周期开始|期末|周期内天数|
| - ------|- ------|- ------|- ------|
| 二〇一八年|2018年2月20日|2018年12月31日|三一四|
| 二○一九年|2019年1月1日|2019年12月31日|三六五|
| 小行星2020|2020年1月1日|2020年2月20日|五十一|

Declare @StartDate date = '2018-02-20', @EndDate date = '2020-02-20';
  
    WITH x AS (SELECT n FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) v(n)),
    Years AS (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Year
        FROM x ones, x tens, x hundreds, x thousands)
  
    SELECT Years.Year,
        CASE 
            WHEN Year(@StartDate) = Years.year THEN @StartDate 
            ELSE DATEFROMPARTS(years.year, 01, 01) 
        END AS periodStart,
        CASE 
            WHEN Year(@EndDate) = Years.year THEN @EndDate 
            ELSE DATEFROMPARTS(years.year, 12, 31) 
        END AS periodEnd,
        DATEDIFF(day, 
            CASE 
                WHEN Year(@StartDate) = Years.year THEN @StartDate 
                ELSE DATEFROMPARTS(years.year, 01, 01) 
            END, 
            CASE 
                WHEN Year(@EndDate) = Years.year THEN @EndDate 
                ELSE DATEFROMPARTS(years.year, 12, 31) 
            END
        ) + 1 AS DaysInPeriod
    FROM Years 
    WHERE Years.Year >= Year(@StartDate) 
        AND Years.Year <= Year(@EndDate)
cqoc49vn

cqoc49vn2#

使用WITH RECURSIVE创建日期范围,然后我们可以使用DATEDIFF轻松计算每年的天数

WITH RECURSIVE dates AS
(
  SELECT min(start_date) as start_date, DATE_FORMAT(min(start_date),'%Y-12-31') as last_day FROM mytable
  UNION ALL
  SELECT DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01'),
  DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-12-31')
  FROM dates
  WHERE DATE_FORMAT(start_date + INTERVAL 1 YEAR,'%Y-01-01')  <= (SELECT MAX(end_date) FROM mytable)
),
cte2 as (
SELECT d.start_date as start_day, if(YEAR(d.start_date) = YEAR(m.end_date), m.end_date, d.last_day) as last_day
FROM dates d, mytable m
)
select *, DATEDIFF(last_day, start_day)+1 as total_days
from cte2;

Demo here

blpfk2vs

blpfk2vs3#

您正在查找DATEDIFF函数。
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff
DATEDIFF()返回expr 1-expr 2,表示为从一个日期到另一个日期的天数。expr 1和expr 2是日期或日期时间表达式。
您可以自由指定例如“2019-01-01”或“2020-01-01”作为DATEDIFF的输入参数。
如果希望SELECT在几年内循环并报告每年的天数,您可能会发现在日历报告表中存储几个1月1日日期非常方便。

相关问题