Question 252814 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Insert Query with expected logic to bifurcate table

I have a table

CREATE TABLE consumer_occurrence_restoration_times (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP
);

where the data is

INSERT INTO consumer_occurrence_restoration_times (consumer_id, start_time, end_time)
VALUES
    (1, '2023-09-24 20:00:00', '2023-09-25 12:00:00'),
    (2, '2023-09-24 21:00:00', '2023-09-25 13:00:00'),
    (1, '2023-09-26 20:00:00', '2023-09-28 02:00:00'),
    (3, '2023-09-25 19:00:00', '2023-09-26 10:00:00'),
    (4, '2023-09-25 21:30:00', '2023-09-27 14:00:00'),
    (5, '2023-09-25 21:30:00', '2023-09-25 22:00:00')

I want to insert this data into another table tbl_bifuraction using a query so that it inserts data for a day, i.e. start_time occurs on same day as end_time not after 00:00:00 hours of next day.

CREATE TABLE tbl_bifurcation (
    consumer_id INT,
    start_time TIMESTAMP,
    end_time TIMESTAMP,
  isdaydiff INT
);

consumer_occurrence_restoration_times table has data like this:

consumer_id   start_time            end_time
1             2023-09-24 20:00:00   2023-09-25 00:00:00
2             2023-09-24 21:00:00   2023-09-25 13:00:00
1             2023-09-26 20:00:00   2023-09-28 02:00:00
3             2023-09-25 19:00:00   2023-09-26 10:00:00
5             2023-09-25 21:30:00   2023-09-25 22:00:00

The insert query inserts the data from the consumer_occurrence_restoration_times table into another table named tbl_bifurcation. However, it needs to split the time intervals into separate days and insert them as individual rows, considering a day from start_time to end_time.
Also, isdaydiff goes 1 if some data has start time as ‘2023-09-26 20:00:00’ and end time is ‘2023-09-27 20:00:00’ but it stays 0 when end time is ‘2023-09-27 00:00:00’.
For example, if start_time is ‘2023-09-26 20:00:00’ and end_time is ‘2023-09-28 02:00:00’, I want to insert rows like this in tbl_bifuraction:

consumer_id start_time end_time isdaydiff
1 2023-09-24 20:00:00 2023-09-25 00:00:00 0
2 2023-09-24 21:00:00 2023-09-25 00:00:00 1
2 2023-09-25 00:00:00 2023-09-25 13:00:00 1
1 2023-09-26 20:00:00 2023-09-27 02:00:00 1
1 2023-09-27 02:00:00 2023-09-28 00:00:00 1
1 2023-09-28 00:00:00 2023-09-28 02:00:00 1
3 2023-09-25 19:00:00 2023-09-26 00:00:00 1
3 2023-09-26 00:00:00 2023-09-26 10:00:00 1
5 2023-09-25 21:30:00 2023-09-25 22:00:00 0

Here, isdaysdiff = 1 (in all rows except first and last) when bifurcation is done, not in all cases.

Condition for updating isdaydiff

isdaydiff = 0 is only used when bifurcation of start date and end date is done, and bifurcation is only done when start date and end date does not fall on same day. It has nothing to do with consumer_id. Each row regardless of any data is a seperate row, no issue whatsoever with previous row. But same consumer will only have different rows when start_date in next row is greater than or equal to previous row end_date with same consumer_id.

The fiddle link is added for bifuracted_query

Tags:

2

Answers


  1. 0

    You may achieve this by dividing the time intervals into distinct days and calculating the isdaydiff flag using a combination of common table expressions (CTEs) and the UNION ALL operator. Try this SQL query to insert the data into the tbl_bifurcation table;

    WITH RECURSIVE DateRange AS (
  SELECT
    consumer_id,
    start_time,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN end_time
      ELSE DATE_TRUNC('day', start_time) + INTERVAL '1 day' - INTERVAL '1 microsecond'
    END AS end_time,
    0 AS isdaydiff
  FROM consumer_occurrence_restoration_times
  UNION ALL
  SELECT
    consumer_id,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN end_time + INTERVAL '1 microsecond'
      ELSE DATE_TRUNC('day', start_time) + INTERVAL '1 day'
    END AS start_time,
    end_time,
    CASE
      WHEN DATE_TRUNC('day', start_time) = DATE_TRUNC('day', end_time)
        THEN 0
      ELSE 1
    END AS isdaydiff
  FROM DateRange
  WHERE start_time < end_time
)
INSERT INTO tbl_bifurcation (consumer_id, start_time, end_time, isdaydiff)
SELECT consumer_id, start_time, end_time, isdaydiff
FROM DateRange
ORDER BY consumer_id, start_time;

    In this query I used recursive CTE DateRange to divide the time intervals into distinct days and determine the isdaydiff flag.This starts with the initial intervals keep adding new rows recursively until the entire interval is covered. The final result is entered into the tbl_bifurcation table after the results of each recursive phase are combined using the UNION ALL operator.
    The tbl_bifurcation table generates the desired output from the query together with the accurate isdaydiff values.

    Hope it works 🙂

    Login or Signup to reply.
  2. 0

    You can first insert the records that don’t require splitting, then UNION ALL that with the selection that does. Use generate_series() to spawn the in-between days. Demo:

    INSERT INTO tbl_bifurcation
SELECT *, 0 as isdaydiff
FROM consumer_occurrence_restoration_times
WHERE start_time::date=end_time::date     --the ones
OR (  end_time::date=(start_time::date+1) --that don't require
    AND end_time::time='00:00:00'::time)  --splitting
UNION ALL  
SELECT consumer_id,
  added_date+case when added_date::date=start_time::date 
                  then start_time::time::interval 
                  else '0s'::interval end,
  added_date+case when added_date::date=end_time::date 
                  then end_time::time::interval 
                  else '1 day'::interval end,
  1 as isdaydiff
FROM (
   SELECT *,generate_series(start_time::date,
                            end_time::date,
                            '1 day'::interval) added_date
   FROM consumer_occurrence_restoration_times
   WHERE start_time::date<>end_time::date       --opposite condition
   AND NOT (end_time::date=(start_time::date+1) --so the ones to split
            AND end_time::time='00:00:00'::time) ) a;

SELECT * FROM tbl_bifurcation
ORDER BY 1,2,3;
    consumer_id start_time end_time isdaydiff
    1 2023-09-24 20:00:00 2023-09-25 00:00:00 0
    1 2023-09-26 20:00:00 2023-09-27 00:00:00 1
    1 2023-09-27 00:00:00 2023-09-28 00:00:00 1
    1 2023-09-28 00:00:00 2023-09-28 02:00:00 1
    2 2023-09-24 21:00:00 2023-09-25 00:00:00 1
    2 2023-09-25 00:00:00 2023-09-25 13:00:00 1
    3 2023-09-25 19:00:00 2023-09-26 00:00:00 1
    3 2023-09-26 00:00:00 2023-09-26 10:00:00 1
    5 2023-09-25 21:30:00 2023-09-25 22:00:00 0
    1. The +case conditionally restores the time part of start and end of the series of timestamps. Everything in between will only use midnights.
    2. You can also use extract() to get dates, times and more from timestamp. Since I only needed dates and times and both date and time have their own types with pre-defined up- and downcasts, I used those instead.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search