Updating timestamps if they fall within a specified date range

Question

I'm currently working with a table which has information about entities and their timestamps. The schema looks like this dat(id, created_time), with id as the primary key.

If a timestamp falls in between Saturday 1am to Monday 1am (exclusive), we'd like to replace the timestamp with Monday 1am.

I was thinking of using a case structure to find instances where the timestamp falls on Saturday and the time is greater than 1:00.00 or timestamp falls on Sunday or timestamp falls on Monday and the time is less than 1:00.00, and assign a hard coded date and time.

I figured this is a common problem and would love any tips for how to make this more streamlined or if there is a function that exists to simplify the process. Thanks!

Answer 1

Well there are a couple of way this can be done. My answers all rely on session vairable week_start being 7 for Sunday, and date_trunc does not support week_iso so there is no always safe solution.

alter session set week_start = 7;

SELECT
    to_char(column1,'DY HH:MI') as t
    ,dayofweek(column1)=1 AND column1::time >= '01:00'::time as sun_after_1am
    ,dayofweek(column1)=2 AND column1::time < '01:00'::time as mon_before_1am
    ,iff(sun_after_1am OR mon_before_1am, dateadd('hour', 25, date_trunc('week', column1)), column1) as iff_answer
    ,case
        when dayofweek(column1)=1 AND column1::time >= '01:00'::time 
            then dateadd('hour', 25, date_trunc('week', column1))
        when dayofweek(column1)=2 AND column1::time < '01:00'::time
            then dateadd('hour', 25, date_trunc('week', column1))
        else column1
    end as case_answer
    ,iff((dayofweek(column1)=1 AND column1::time >= '01:00'::time) OR (dayofweek(column1)=2 AND column1::time < '01:00'::time), dateadd('hour', 25, date_trunc('week', column1)), column1) as monster_iff_answer
    ,column1 between dateadd('hour', 1, date_trunc('week', column1)) 
        and dateadd('hour', 25, date_trunc('week', column1))    
FROM VALUES 
    ('2022-03-31 09:09'::datetime),
    ('2022-03-28 08:09'::datetime),
    ('2022-03-28 00:12'::datetime),
    ('2022-03-27 23:33'::datetime),
    ('2022-03-27 01:01'::datetime),
    ('2022-03-27 00:02'::datetime)
ORDER BY column1;

T	SUN_AFTER_1AM	MON_BEFORE_1AM	IFF_ANSWER	CASE_ANSWER	MONSTER_IFF_ANSWER	BETWEEN_LOGIC
Sun 00:02	FALSE	FALSE	2022-03-27 00:02:00.000	2022-03-27 00:02:00.000	2022-03-27 00:02:00.000	FALSE
Sun 01:01	TRUE	FALSE	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	TRUE
Sun 23:33	TRUE	FALSE	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	TRUE
Mon 00:12	FALSE	TRUE	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	2022-03-28 01:00:00.000	TRUE
Mon 08:09	FALSE	FALSE	2022-03-28 08:09:00.000	2022-03-28 08:09:00.000	2022-03-28 08:09:00.000	FALSE
Thu 09:09	FALSE	FALSE	2022-03-31 09:09:00.000	2022-03-31 09:09:00.000	2022-03-31 09:09:00.000	FALSE

So there is an two tests for dayofweek and a time compare, which are OR'ed in an IFF

    ,dayofweek(column1)=1 AND column1::time >= '01:00'::time as sun_after_1am
    ,dayofweek(column1)=2 AND column1::time < '01:00'::time as mon_before_1am
    ,iff(sun_after_1am OR mon_before_1am, dateadd('hour', 25, date_trunc('week', column1)), column1) as iff_answer

then there is the CASE, which I think actually reads nicer:

    ,case
        when dayofweek(column1)=1 AND column1::time >= '01:00'::time 
            then dateadd('hour', 25, date_trunc('week', column1))
        when dayofweek(column1)=2 AND column1::time < '01:00'::time
            then dateadd('hour', 25, date_trunc('week', column1))
        else column1
    end as case_answer

then there is the IFF mashed all together, which is gross:

,iff((dayofweek(column1)=1 AND column1::time >= '01:00'::time) OR (dayofweek(column1)=2 AND column1::time < '01:00'::time), dateadd('hour', 25, date_trunc('week', column1)), column1) as monster_iff_answer

and then there is a BETWEEN that just uses DATE_TRUNC

 ,column1 between dateadd('hour', 1, date_trunc('week', column1)) 
        and dateadd('hour', 25, date_trunc('week', column1)) as between_logic  

anyways, you said update, so this give me a unified result, which a SELECT or VIEW would need can be separated into a WHERE clause

UPDATE table_name
    SET date_time_column = dateadd('hour', 25, date_trunc('week', date_time_column ))
WHERE date_time_column BETWEEN dateadd('hour', 1, date_trunc('week', date_time_column)) 
        AND dateadd('hour', 25, date_trunc('week', date_time_column))