时间戳

首页 > 解决方案 > MySQL 中的 DATEDIFF，范围扩大了新年

mysql - MySQL 中的 DATEDIFF，范围扩大了新年

问题描述

我有一个包含假期请求的数据库表。我现在想计算用户在某一年请求了多少天。到目前为止，我这样做了：

桌子：

CREATE TABLE `holidays` (
  `id` int(11) NOT NULL,
  `user` int(11) NOT NULL,
  `begin` date NOT NULL,
  `end` date NOT NULL,
  `comment_user` text NOT NULL,
  `entered_at` int(11) NOT NULL  
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

获得一年中的所有假期：

SELECT SUM((DATEDIFF(end, begin) + 1)) AS days 
FROM holidays 
WHERE user = :user AND begin LIKE '2021%'

但是，如果开始日期在前一年（例如 2020 年 12 月 30 日）或结束日期在明年（例如 2022 年 1 月 5 日），我该怎么办，所以请求会延长到除夕。

示例：

如果我有一个从12/30/2020 到 01/02/2021的假期请求，我想只计算其中的两天，而不是全部四天。只有2021年的日子

是否有可能将DATEDIFF计算限制为 2021 年 1 月 1 日和 2021 年 12 月 31 日，但在那一年影响所有请求？我怎样才能把它放在一个mysql查询中？我在谷歌上找不到任何解决方案。

如果有人遇到类似问题并解决了，那就太好了。

提前致谢，托拜厄斯

标签： mysql

解决方案

我创建了一个这样的示例数据库（FIDDLE）：

CREATE TABLE `holidays` (
  `begin` date DEFAULT NULL,
  `end` date DEFAULT NULL
)
INSERT INTO `holidays` VALUES 
    ('2020-12-28','2021-01-05'),
    ('2020-12-05','2020-12-06'),
    ('2021-01-06','2021-01-06');

这个 SQL 语句：

select 
   begin, 
   CASE WHEN year(end)>year(begin) then ADDDATE(makedate(year(`end`),1),INTERVAL -1 DAY) else `end` end as END , 
   year(begin) as year
from holidays
union all
select
   makedate(year(`end`),1),
   `end`,
   year(`end`)
from holidays
where year(`begin`)<>year(`end`);

将作为输出：

+------------+------------+------+
| begin      | END        | year |
+------------+------------+------+
| 2020-12-28 | 2020-12-31 | 2020 |
| 2020-12-05 | 2020-12-06 | 2020 |
| 2021-01-06 | 2021-01-06 | 2021 |
| 2021-01-01 | 2021-01-05 | 2021 |
+------------+------------+------+

顺便说一句：永远不应该使用或至少尽量避免在表定义中使用保留字（如“开始”、“结束”）

推荐阅读