SQL Return 2 Row From 1-CodePudding

I am not sure if this is possible or possible efficiently, but here is what I would like to do.

I have a table with the following columns: Date and Value

Date                  Value
06/09/2022 11:30PM    110
06/10/2022 12:45AM    100
06/10/2022 1:30AM     300

I want to do a select query that when a value spans 2 days between 12:00 AM and 12:59AM it returns 2 data points (Does not modify the underlying data) where the value each day is weighted by how much into the hour the timestamp that falls into this window is.

So in this case I would like my query to return:

Date                  Value
06/09/2022 11:30PM    110
06/09/2022 11:59PM    25
06/10/2022 12:59AM    75
06/10/2022 1:30AM     300

The questions I have are:

Is it possible to write a function or use SQL statements to return 2 rows from 1 without modifying the underlying data?
Is there a way to achieve this withing Postgres vs. in application?

Thanks

CodePudding user response：

One way to get more rows from table is to concatenate several results using union:

create table d (date timestamp, value float);
insert into d values ('2022/06/09 11:30PM',110),('2022/06/10 12:45AM',100),('2022/06/10 1:30AM',300);

select date as "Date", value as "Value" from d where date::time >= '1:00' 
union all
select date_trunc('day',date) '0:59',
    value * extract (epoch from date::time)/3600
    from d where date::time < '1:00' 
union all
select date_trunc('day',date)-'0:01'::interval,
     value * (3600-extract (epoch from date::time))/3600
     from d where date::time < '1:00'
order by 1;

result:

        Date         | Value 
--------------------- -------
 2022-06-09 23:30:00 |   110
 2022-06-09 23:59:00 |    25
 2022-06-10 00:59:00 |    75
 2022-06-10 01:30:00 |   300

But you want this as a function? I could use proceureal code here, but I already have it as a query, so I'll just use that.

create function mangle(inout "Date" timestamp,inout "Value" float)
  returns setof record language sql as $$
    select "Date", "Value" where "Date"::time >= '1:00' 
    union all
    select date_trunc('day',"Date") '0:59',
        "Value" * extract (epoch from "Date"::time)/3600
        where "Date"::time < '1:00' 
    union all
    select date_trunc('day',"Date")-'0:01'::interval,
         "Value" * (3600-extract (epoch from "Date"::time))/3600
         where "Date"::time < '1:00'
    order by 1;
$$;

select (mangle(date,value)).* from d;

As you can see mixed-case identifiers get messy fast, so I reccomend you stick to lower case, unless you absolutely need them.

CodePudding user response：

Provided you never see an entry on 23:59 on a day before an entry in the midnight hour, break it down as follows:

Find values to be split and figure out the share:

with to_be_split as (
  select date, value, 
         extract(minute from date)/60 as today_share,
         (60 - extract(minute from date))/60 as yesterday_share 
    from my_table
   where extract(hour from date) = 0
),

Do a lateral cross join into a values pseudo-table to create the extra rows:

splits as (
  select sub.date, round(tbs.value * sub.share) as value
    from to_be_split tbs
         cross join lateral (
           values (date, today_share),
                  (date_trunc('day', date) - interval '1 minute', yesterday_share)
         ) as sub(date, share)
)

Union the result back with records that don't need splitting:

select date, value
  from splits
union all
select date, value
  from my_table
 where extract(hour from date) != 0
 order by date;

db<>fiddle here