Split the dates which is rolling past 00:00 hrs into two rows

Question

I want to split a single row into multiple rows based on time at 00:00:00 hrs in snowflake. below are the example. StartDate and EndDate is in Timestamp_NTZ(9) datatype.

SrNo	NotificateDate	StartDate	EndDate
1	2018-08-20	2018-08-20 07:27:00.000	2018-08-21 16:23:00.000
2	2018-08-21	2018-08-21 03:36:00.000	2018-08-21 04:36:00.000
3	2018-08-21	2018-08-21 04:36:00.000	2018-08-21 21:35:00.000

Expected output is below:

SrNo	NotificationDate	StartDate	EndDate
1	2018-08-20	2018-08-20 07:27:00.000	2018-08-20 23:59:59.000
2	2018-08-21	2018-08-21 00:00:00.000	2018-08-21 16:23:00.000
3	2018-08-21	2018-08-21 03:36:00.000	2018-08-21 04:36:00.000
4	2018-08-21	2018-08-21 04:36:00.000	2018-08-21 21:35:00.000

I need to split on the next day 00:00:00 hrs if the time is overlapping to the next day

I tried to find the date diff between the startdate and enddate but doesnt work as expected.

Answer 1

Not sure if it covers all scenarios, I tried to make a condition comparing the day part in endDate and StartDate columns:

Try this:

WITH
  original AS (
    SELECT 
        startdate,
        enddate
    FROM times --replace with the name of your table
  ),
  before_midnight AS (
    SELECT   
        startdate, 
        CASE 
          WHEN DATE_PART('DAY', StartDate) < DATE_PART('DAY', EndDate) THEN
            DATEADD('second', -1, TO_TIMESTAMP(TO_DATE (EndDate)))
          ELSE enddate
        END AS enddate
    FROM original
  ),
  after_midnight AS (
    SELECT 
        CASE 
          WHEN DATE_PART('DAY', StartDate) < DATE_PART('DAY', EndDate) THEN
            TO_TIMESTAMP(TO_DATE(enddate))
          ELSE startdate
        END AS startdate,
        enddate 
    FROM original
  )
SELECT 
    ROW_NUMBER() OVER (ORDER BY startDate) AS SrNo,
    DATE(startDate) AS NotificationDate,
    startDate,
    endDate 
FROM (
    SELECT * FROM before_midnight
    UNION DISTINCT
    SELECT * FROM after_midnight
) AS combined
ORDER BY startDate;