如何在Oracle SQL中检查两个日期之间存在多少个星期日

**我想查看两个日期之间的“星期日”的数量

例如，**

创建日期21-10-23 06：00：00.00000000 PM星期六
发布日期23-10-23 06：00：00.00000000 PM星期一

我想看看这中间有多少个星期天，在这种情况下，1
这是我的代码，案例陈述要有重点，

SUM(
    CASE
        WHEN EXTRACT(HOUR FROM (UPDATEDDATE - CREATEDDATE)) <= 4   -- hours should be < or = to 4
        AND (
            EXTRACT(DAY FROM (UPDATEDDATE - CREATEDDATE)) -        -- days in between
            (
                SELECT COUNT(*)
                FROM DUAL
                WHERE TO_CHAR(CREATEDDATE + LEVEL, 'D') = '1'      -- is sunday?
                CONNECT BY CREATEDDATE + LEVEL <= UPDATEDDATE      -- checking till the end date
            ) = 0
        )
        THEN 1
        ELSE 0
    END
) AS "0_4",

字符串
我有两个专栏
创建日期-> 04-11-23 06：00：00.00000000 PM星期六
发布日期-> 04-11-23 08：00：00.00000000 PM星期六
我想检查日期之间的小时数是否小于4或相等，并且它之间的天数有“星期日”，如果有任何星期日，我想计算有多少个“星期日”，如果从两个日期之间的天数，
在这种情况下，两个日期之间的小时数是，

EXTRACT(HOUR FROM (UPDATEDDATE - CREATEDDATE)) -- 2hrs

型
两个日期之间的天数

EXTRACT(DAY FROM (UPDATEDDATE - CREATEDDATE)) -- 0 days

型
这里的条件是不满意的，而且如果有星期天之间，它是不工作，是代码逻辑是正确的或不？，需要帮助。谢谢。

您不需要使用行生成器来查找所有日期，并且可以使用ISO周数计算星期日的数量（这与您可能使用的任何语言或地区设置无关）：

SELECT created_date,
       updated_date,
       -- Number of Sundays in the start week
       CASE
       WHEN TRUNC(created_date) - TRUNC(created_date, 'IW') <= 6 -- Sunday
       THEN 1
       END
       +
       -- Number of full weeks
       (TRUNC(updated_date, 'IW') - TRUNC(created_date, 'IW')) / 7
       -- Do not double count the first week
       - 1
       +
       -- Number of Sundays in the end week
       CASE
       WHEN TRUNC(updated_date) - TRUNC(updated_date, 'IW') < 6 -- Sunday
       THEN 0
       ELSE 1
       END
       AS number_of_sundays
FROM   table_name

字符串
其中，对于样本数据：

CREATE TABLE table_name (created_date, updated_date) AS
  SELECT TIMESTAMP '2023-10-21 18:00:00.000000000',
         TIMESTAMP '2023-10-23 20:00:00.000000000'
  FROM   DUAL
UNION ALL
  SELECT TIMESTAMP '2023-10-21 20:00:00.000000000',
         TIMESTAMP '2023-10-22 04:00:00.000000000'
  FROM   DUAL;

型
产出：
| 创建日期|发布日期|星期天的数量|
| --|--|--|
| 2023-10-21 18：00：00.00000000| 2023-10-23 20：00：00.00000000| 1 |
| 2023-10-21 20：00：00.00000000| 2023-10-22 04：00：00.00000000| 1 |
如果你想找到两个时间戳之间的差异，不包括星期天，那么你还需要考虑你可能没有一个完整的星期天期间，可以用途：

SELECT created_date,
       updated_date,
       updated_date
       - created_date
       - (
           -- Time on Sundays in the start week
           LEAST(
             TRUNC(created_date, 'IW') + INTERVAL '7' DAY - created_date,
             INTERVAL '1' DAY
           )
           +
           -- Sundays in the full weeks
           (
             TRUNC(updated_date, 'IW') - INTERVAL '7' DAY - TRUNC(created_date, 'IW')
           ) / 7 * INTERVAL '1' DAY
           +
           -- Time on Sunday in the last week
           GREATEST(
             updated_date - TRUNC(updated_date, 'IW') - INTERVAL '6' DAY,
             INTERVAL '0' DAY
           )
         )
       AS time_difference_exclude_sunday
FROM   table_name

型
对于样本数据，输出：
| 创建日期|发布日期|时间_差异_排除_星期日|
| --|--|--|
| 2023-10-21 18：00：00.00000000| 2023-10-23 20：00：00.00000000| 000000001 02：00：00.00000000|
| 2023-10-21 20：00：00.00000000| 2023-10-22 04：00：00.00000000| +000000000 04：00：00.00000000|
如果你想过滤行，只显示那些创建和更新时间之间有4个小时或更少的时间，那么你可以使用用途：

SELECT *
FROM   table_name
WHERE  updated_date
       - created_date
       - (
           -- Time on Sundays in the start week
           LEAST(
             TRUNC(created_date, 'IW') + INTERVAL '7' DAY - created_date,
             INTERVAL '1' DAY
           )
           +
           -- Sundays in the full weeks
           (
             TRUNC(updated_date, 'IW') - INTERVAL '7' DAY - TRUNC(created_date, 'IW')
           ) / 7 * INTERVAL '1' DAY
           +
           -- Time on Sunday in the last week
           GREATEST(
             updated_date - TRUNC(updated_date, 'IW') - INTERVAL '6' DAY,
             INTERVAL '0' DAY
           )
         )
      <= INTERVAL '4' HOUR;

型
产出：
| 创建日期|发布日期|
| --|--|
| 2023-10-21 20：00：00.00000000| 2023-10-22 04：00：00.00000000|
fiddle
至于你的问题
代码逻辑是否正确？
不，不是的。
如果你有日期'2023-10-21 20:00:00.000000000'和'2023-10-22 00:00:00.000000000'，那么最后一天是星期天，并且updated_date的时间分量早于created_date的时间分量，那么检查CREATEDDATE + LEVEL <= UPDATEDDATE将跳过最后一个星期天。
它也不考虑星期天的分数。

如何在Oracle SQL中检查两个日期之间存在多少个星期日

1条答案

相关问题

热门标签

最新问答