PostgreSQL中基于工作日的任务创建日两个工作日后截止时间判断逻辑实现问询

Got it, let's break down how to solve this problem exactly as you described. You need to calculate the deadline as 23:59:59 on the second business day after creation_date, then check if that deadline has passed relative to the current time (now()).

First, let's recap the core requirements to make sure we're aligned:

• If creation_date is NULL, return FALSE (since we can't calculate a deadline without a creation time)
• For non-NULL creation_date, compute the date of the 2nd business day after its date part
• Set the time of that deadline to 23:59:59 to get a full timestamp
• Compare this deadline to the current time: return TRUE if the deadline is earlier than now, FALSE otherwise

Example Validation #

Your test case checks out perfectly:

If creation_date is 11/08/2021 10:00 (a Monday), the 2nd business day is Wednesday, 11/10/2021. The deadline is 11/10/2021 23:59:59.

• Current time 11/11/2021 00:00 → deadline is earlier → return TRUE
• Current time 11/10/2021 23:59 → deadline is later → return FALSE

Implementation by Database #

The approach varies slightly depending on your database system, since each has different built-in functions for handling business days.

1. PostgreSQL

If you have the pg_businessdays extension installed (a common utility for business date calculations), this becomes straightforward:

SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN FALSE
        -- Add 2 business days, set time to 23:59:59, compare to current time
        ELSE (d.creation_date + business_days(2))::DATE + '23:59:59'::INTERVAL < NOW()
    END AS limit_reached
FROM the_table d;

If you can't use extensions, you can generate dates and filter out weekends manually:

SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN FALSE
        ELSE (
            -- Get the 2nd business day after creation_date, set time to 23:59:59
            (SELECT (creation_date::DATE + n)::TIMESTAMP + '23:59:59'::INTERVAL
             FROM generate_series(1, 10) AS n
             -- Exclude Sundays (0) and Saturdays (6)
             WHERE EXTRACT(DOW FROM (creation_date::DATE + n)) NOT IN (0, 6)
             ORDER BY n
             LIMIT 1 OFFSET 1) -- Pick the 2nd valid business day
        ) < NOW() AS limit_reached
FROM the_table d;

2. MySQL (8.0+)

MySQL has a WORKDAY_ADD function that simplifies business day calculations:

SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN FALSE
        -- Add 2 business days, append 23:59:59 time, compare to now()
        ELSE TIMESTAMP(WORKDAY_ADD(DATE(creation_date), 2), '23:59:59') < NOW()
    END AS limit_reached
FROM the_table d;

If WORKDAY_ADD isn't available, you can calculate weekends manually:

SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN FALSE
        ELSE (
            DATE_ADD(
                DATE(creation_date),
                INTERVAL (
                    2 +
                    -- Add extra days for weekends that fall within the interval
                    FLOOR((DATE(creation_date) + INTERVAL 2 DAY - DATE(creation_date)) / 7) * 2 +
                    CASE WHEN WEEKDAY(DATE(creation_date)) + 2 >= 5 THEN 2 ELSE 0 END
                ) DAY
            ) + INTERVAL '23:59:59' HOUR_SECOND
        ) < NOW() AS limit_reached
FROM the_table d;

3. SQL Server 2022+

SQL Server 2022 introduced the DATEADD_WORKDAY function, making this task easy:

SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN 0 -- SQL Server uses bit/int for boolean values
        ELSE CASE WHEN DATEADD_WORKDAY(2, CAST(d.creation_date AS DATE)) + CAST('23:59:59' AS TIME) < GETDATE() THEN 1 ELSE 0 END
    END AS limit_reached
FROM the_table d;

For older SQL Server versions, create a custom function to add workdays:

-- First create the helper function
CREATE FUNCTION dbo.AddWorkdays (@StartDate DATE, @Workdays INT)
RETURNS DATE
AS
BEGIN
    DECLARE @CurrentDate DATE = @StartDate;
    DECLARE @DaysAdded INT = 0;

    WHILE @DaysAdded < @Workdays
    BEGIN
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
        -- Exclude Sunday (1) and Saturday (7) - adjust if your server uses a different first day of week
        IF DATEPART(WEEKDAY, @CurrentDate) NOT IN (1, 7)
            SET @DaysAdded = @DaysAdded + 1;
    END

    RETURN @CurrentDate;
END;
GO

-- Now use it in your query
SELECT
    d.creation_date,
    CASE
        WHEN d.creation_date IS NULL THEN 0
        ELSE CASE WHEN CAST(dbo.AddWorkdays(CAST(d.creation_date AS DATE), 2) AS DATETIME) + CAST('23:59:59' AS DATETIME) < GETDATE() THEN 1 ELSE 0 END
    END AS limit_reached
FROM the_table d;

内容的提问来源于stack exchange，提问作者Jack Torres