Events Dates as dateranges generated colum #366
Comments
|
I think this should be solved with a |
|
sounds great.... what would be nice if we create a We have to consider we have this types of EventDates
|
|
The following PG function returns the DateRange as a CREATE OR REPLACE FUNCTION public.json_2_tsrange_array(jsonarray jsonb)
RETURNS tsrange[]
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$ begin if jsonarray <> 'null' then return (
select
array(
select
tsrange(
(event ->> 'From'):: timestamp,
(event ->> 'To'):: timestamp + interval '1 day'
)
from
jsonb_array_elements(jsonarray) as event
where
(event ->> 'From'):: timestamp <= (event ->> 'To'):: timestamp + interval '1 day'
)
);
else return null;
end if;
end;
$function$
Usage: select json_2_tsrange_array(data->'EventDate') from events;
|
|
Variant that includes the time information: CREATE OR REPLACE FUNCTION public.json_2_tsrange_array(jsonarray jsonb)
RETURNS tsrange[]
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$ begin if jsonarray <> 'null' then return (
select
array(
select
tsrange(
( (event ->> 'From')::timestamp + (event ->> 'Begin')::time),
( (event ->> 'To')::timestamp + (event ->> 'End')::time)
)
from
jsonb_array_elements(jsonarray) as event
where
(event ->> 'From')::timestamp + (event ->> 'Begin')::time < (event ->> 'To')::timestamp + (event ->> 'End')::time
)
);
else return null;
end if;
end;
$function$ |
|
It seems that the correct solution for the date range queries ist to use In order to convert the CREATE OR REPLACE FUNCTION convert_tsrange_array_to_tsmultirange(tsrange_array tsrange[])
RETURNS tsmultirange
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $$
DECLARE
result tsmultirange := tsmultirange();
tsr tsrange;
BEGIN
IF tsrange_array IS NOT NULL THEN
-- Durchlaufen des tsrange-Arrays
FOREACH tsr IN ARRAY tsrange_array
LOOP
-- Hinzufügen des tsrange-Elements zum tsmultirange
result := result + tsmultirange(tsrange(tsr));
END LOOP;
END IF;
RETURN result;
END;
$$;To speed up the query the following generated column can be defined: ALTER TABLE events ADD IF NOT EXISTS gen_eventdates tsmultirange GENERATED ALWAYS AS (convert_tsrange_array_to_tsmultirange(json_2_tsrange_array(data#>'{EventDate}'))) stored;Then the following query can be written to query for a date range that overlaps with all the date ranges defined in the event document: select id from events where gen_eventdates && tsrange('[2023-09-07 10:00,2023-09-07 12:30]');Which returns at the time of writing 498 events. |
|
Be aware that the |
I can be wrong on that as I can't find any documentation for |
I've testet it with the following SQL and it seems like the date ranges get merged: Result
And with the Result
So in fact the date ranges get merged! So my previous statement holds:
|
|
it looks very good, i will make further tests and deploy on test |
Hi
an Event is currently defined with a Startdate and an Enddate. The actual datefilter simply returns the Event by checking if the passed daterange is IN the Event start begin daterange....
This behaviour is not always correct because there exists Events that occurs at example on 14.07 / 21.07 / 28.07
So the begindate is set to 14.07 and enddate 28.07. Now if the query from 16.07 to 20.07 with the actual datamodel the event is returned because this daterange is in the defined
This Eventdates are stored in the
EventDatePropertyexample
"EventDate": [ { "To": "2023-04-21T00:00:00", "End": "16:14:00", "From": "2023-04-21T00:00:00", "Begin": "15:00:00", ... }, { "To": "2023-04-25T00:00:00", "End": "16:14:00", "From": "2023-04-25T00:00:00", "Begin": "15:00:00", ... } ],another possibility is to have
"EventDate": [ { "To": "2023-04-25T00:00:00", "End": "16:14:00", "From": "2023-04-21T00:00:00", "Begin": "15:00:00", ... }, { "To": "2023-04-30T00:00:00", "End": "16:14:00", "From": "2023-04-27T00:00:00", "Begin": "15:00:00", ... } ],The idea is to add the defined Dateranges in the EventDateField as a
tsrange[]and querying this fieldsThe text was updated successfully, but these errors were encountered: