As far I can see, r-tree indexes are used today mostly for geographic systems, for spatial searches. I need some very similar, but not in space, but in 1-D time series. Imagine a *huge* time series, for example floating point data of a billions of point. *I need to answer quickly questions like* > *"what was the maximal value in the interval (1345223, 23234235)"?* Note, the DB is huge, a simple SELECT MAX(value) WHERE id BETWEEN 1345223 AND 23234235 is not acceptable - at least not if it needs a linear scanning of the interval. It must happen in same type of tricky index, most likely r-tree. Maybe it helps (but more likely not): as it is a time series table, it is inherently single-column. Other index types or other database engines are okay as well. I am investigating postgresql only because it is the smartest open source engine I know.

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to approximate the result with "generate_series".u Is there another way faster and more precise? Interval In: Start---------------------------------------End E.begin_date ------ E.end_date -> E.end_date - E.begin_date Start--------------------------------End E.begin_date -------------------E.end_date -> End - E.begin_date Start--------------------------------End E.begin_date -----------------------E.end_date -> E.end_date - Start Start------------------End E.begin_date-------------------------------------E.end_date -> End - Start Interval In on several Days with generate_series Day 1 Day 2 etc.... | | Start-------End | Start---------End | Start---------End E.begin_date--------------------------------------------------------------E.end_date Interval Out: Start--------End E.begin_date ------------------- E.end_date -> (Start - E.begin_date) + (E.end_date - End) Start--------------------------------End E.begin_date -------------------E.end_date -> E.end_date - End Start--------------------------------End E.begin_date -----------------------E.end_date -> Start - .begin_date Start--------End E.begin_date--------E.end_date -> E.end_date - E.begin_date Interval Out on several Days with generate_series Day 1 Day 2 etc.... | | Start-------End | Start---------End | Start---------End E.begin_date--------------------------------------------------------------E.end_date This is my sql request: select * FROM (SELECT sum( case when (begin_date AT TIME ZONE 'Europe/Paris')::date = (end_date AT TIME ZONE 'Europe/Paris')::date -- same days then case -- ------time_begin------------------------------------------------------------------------time_end---- -- begin_date------------------------------------------------end_date when (begin_date AT TIME ZONE 'Europe/Paris')::time >= '08:00'::time AND (end_date AT TIME ZONE 'Europe/Paris')::time = '08:00'::time AND (begin_date AT TIME ZONE 'Europe/Paris')::time = '18:00'::time then '18:00'::time - (begin_date AT TIME ZONE 'Europe/Paris') ::time -- -----------------time_begin------------------------------------------------------------------------time_end---- -- begin_date-----------------------------------end_date when (end_date AT TIME ZONE 'Europe/Paris')::time >= '08:00'::time AND (end_date AT TIME ZONE 'Europe/Paris')::time = '18:00'::time then '18:00'::time - '08:00'::time END when (begin_date AT TIME ZONE 'Europe/Paris')::date = '08:00'::time AND (m::time AT TIME ZONE 'Europe/Paris') = '2019-12-01T00:00:00'::date AND ("event"."begin_date" AT TIME ZONE 'Europe/Paris')::date '18:00'::time)) )) order by total_included desc) as included, (SELECT sum( case when (begin_date AT TIME ZONE 'Europe/Paris')::date = (end_date AT TIME ZONE 'Europe/Paris')::date -- same day then case -- -----------------------------------------------------------------time_begin---------------------------------time_end---- -- begin_date-------------------------------end_date -- or -- -------------------------------time_begin---------------------------------time_end----------- -- begin_date----------------------------------------------------------------------end_date when ((begin_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time) AND ((end_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time) then case when ((begin_date AT TIME ZONE 'Europe/Paris')::time '18:00'::time AND (end_date AT TIME ZONE 'Europe/Paris')::time > '18:00'::time then end_date::time - begin_date::time when (begin_date AT TIME ZONE 'Europe/Paris')::time '18:00'::time then ('08:00'::time - (begin_date AT TIME ZONE 'Europe/Paris')::time) + ((end_date AT TIME ZONE 'Europe/Paris')::time - '18:00'::time) end -- --------------------------------------time_begin---------------------------------time_end---- -- begin_date-----------------------------------end_date when ((begin_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time) AND ((end_date AT TIME ZONE 'Europe/Paris')::time BETWEEN '08:00'::time AND '18:00'::time) then '08:00'::time - (begin_date AT TIME ZONE 'Europe/Paris')::time -- ----------time_begin---------------------------------time_end---- -- begin_date-----------------------------------end_date when ((begin_date AT TIME ZONE 'Europe/Paris')::time BETWEEN '08:00'::time AND '18:00'::time) AND ((end_date AT TIME ZONE 'Europe/Paris')::time NOT BETWEEN '08:00'::time AND '18:00'::time) then (end_date AT TIME ZONE 'Europe/Paris')::time - '18:00'::time end when (begin_date AT TIME ZONE 'Europe/Paris')::date = '08:00'::time AND (m AT TIME ZONE 'Europe/Paris')::time = '2019-12-01T00:00:00'::date AND ("event"."date_creation" AT TIME ZONE 'Europe/Paris')::date '08:00'::time) AND ((event.end_date AT TIME ZONE 'Europe/Paris')::time < '18:00'::time)) )) order by total_excluded desc) as excluded Data Sample for interval IN ('8am', '6pm') for Wednesday Thursday: |---------|------------------|------------------|------------------| | id | begin_date | end_date | Result(Duration) | |---------|------------------|------------------|------------------| | 1 | 2020-01-01 10:00 | 2020-01-01 12:00 | 02:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 2 | 2020-01-01 10:00 | 2020-01-01 20:00 | 08:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 3 | 2020-01-01 07:00 | 2020-01-01 14:00 | 06:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 4 | 2020-01-01 07:00 | 2020-01-01 19:00 | 10:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 5 | 2020-01-01 08:00 | 2020-01-03 18:00 | 20:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 6 | 2020-01-01 09:00 | 2020-01-05 17:00 | 16:00:00 | |---------|------------------|------------------|------------------| Data Sample for interval OUT ('8am', '6pm') for Wednesday Thursday: |---------|------------------|------------------|------------------| | id | begin_date | end_date | Result(Duration) | |---------|------------------|------------------|------------------| | 1 | 2020-01-01 10:00 | 2020-01-01 12:00 | 00:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 2 | 2020-01-01 10:00 | 2020-01-01 20:00 | 02:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 3 | 2020-01-01 07:00 | 2020-01-01 14:00 | 01:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 4 | 2020-01-01 07:00 | 2020-01-01 19:00 | 02:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 5 | 2020-01-01 07:00 | 2020-01-03 18:00 | 02:00:00 | |---------|------------------|------------------|------------------| |---------|------------------|------------------|------------------| | 6 | 2020-01-01 09:00 | 2020-01-05 17:00 | 02:00:00 | |---------|------------------|------------------|------------------| The input parameters are: * start date and end date * start time and end time * a list of the days of the week To reach a result where we have the total duration of events within the interval, but also the total duration of events outside the interval of hours. All this for a list of days of the given week. Days of the week means that if an event lasts several days say from Monday to Wednesday, but in the filter there are only Monday and Tuesday, the duration of Wednesday will not be counted. The current query works but to handle events that take place over several days I use generate_series, which is not very efficient. The question is how to improve this query.

I am looking for a standard method, a library or maybe best practice approach that can generate a time-interval/state-duration report from a time/state series. This seems like a common reporting requirement for time/state data, and I would assume there is framework support for this. I am specifically seeking a function or library to achieve this, rather than a custom SQL solution, but maybe there is only a custom way to do it. Below, I provide (in postgres) a simplified structure and sample data to facilitate the discussion. **Creating the input table**

-- Create the time_state table
CREATE TABLE "time_state" (
  "state_time" TIMESTAMP WITH TIME ZONE NOT NULL,
  "state_name" VARCHAR(50) NOT NULL
);

**Adding example input data**

INSERT INTO "time_state" ("state_time", "state_name") VALUES
('2023-07-01 21:00:00+00', 'OFF'),
('2023-07-01 21:15:00+00', 'IDLE'),
('2023-07-01 21:30:00+00', 'HEATING'),
('2023-07-01 21:45:00+00', 'COOLING'),
('2023-07-01 22:00:00+00', 'FAULT'),
('2023-07-01 22:15:00+00', 'OFF'),
('2023-07-01 22:30:00+00', 'IDLE'),
('2023-07-01 22:45:00+00', 'HEATING'),
('2023-07-01 23:00:00+00', 'COOLING'),
('2023-07-01 23:15:00+00', 'FAULT'),
('2023-07-01 23:30:00+00', 'OFF'),
('2023-07-01 23:45:00+00', 'IDLE'),

('2023-07-02 00:00:00+00', 'HEATING'),
('2023-07-02 00:15:00+00', 'COOLING'),
('2023-07-02 00:30:00+00', 'FAULT'),
('2023-07-02 00:45:00+00', 'OFF'),
('2023-07-02 01:00:00+00', 'IDLE'),
('2023-07-02 01:15:00+00', 'HEATING'),
('2023-07-02 01:30:00+00', 'COOLING'),
('2023-07-02 01:45:00+00', 'FAULT'),
('2023-07-02 02:00:00+00', 'OFF'),
('2023-07-02 02:15:00+00', 'IDLE'),
('2023-07-02 02:30:00+00', 'HEATING'),
('2023-07-02 02:45:00+00', 'COOLING'),
('2023-07-02 03:00:00+00', 'FAULT'),
('2023-07-02 03:15:00+00', 'OFF'),
('2023-07-02 03:30:00+00', 'IDLE'),
('2023-07-02 03:45:00+00', 'HEATING'),
('2023-07-02 04:00:00+00', 'COOLING');

**Result of the report that shows the duration of each state within each hour should be then:**

date_with_hour	state_name	state_duration_in_minutes
2023-07-01 21:00:00	COOLING	15.0
2023-07-01 21:00:00	HEATING	15.0
2023-07-01 21:00:00	IDLE	15.0
2023-07-01 21:00:00	OFF		15.0
2023-07-01 22:00:00	COOLING	15.0
2023-07-01 22:00:00	FAULT	15.0
2023-07-01 22:00:00	HEATING	15.0
2023-07-01 22:00:00	IDLE	15.0
2023-07-01 23:00:00	COOLING	15.0
2023-07-01 23:00:00	FAULT	15.0
2023-07-01 23:00:00	HEATING	15.0
2023-07-01 23:00:00	IDLE	15.0
2023-07-02 00:00:00	COOLING	15.0
2023-07-02 00:00:00	FAULT	15.0
2023-07-02 00:00:00	HEATING	15.0
2023-07-02 00:00:00	IDLE	15.0

Thanks Dave

I have two tables that allocate employees to cost centers. The first table (CCmain) shows the primary cost center. If an employee is assigned to more than one cost center, the second table (CCfraction) shows what fraction of the employee is assigned to which cost center. For a BI evaluation, I need the splitting in witch timespan an employee is in which cost center. What query will give this result? This illustrates one situation: Time CCmain: CCfraction: Result: **Sample Data** CREATE TABLE CCmain( ValidFrom date NOT NULL, ValidTo date NOT NULL, Costcenter int NOT NULL, PN int NULL ); CREATE TABLE CCfraction( PN int NOT NULL, ValidFrom date NOT NULL, ValidTo date NOT NULL, Costcenter int NOT NULL, Fraction int NOT NULL ); GO INSERT INTO CCmain (ValidFrom,ValidTo,Costcenter,PN) VALUES ('2020-10-01','2020-12-31',100,1), ('2021-01-01','2021-12-31',200,1), ('2022-01-01','2022-09-30',100,1), ('2023-01-01','2023-03-31',300,1); INSERT INTO CCfraction (PN,ValidFrom,ValidTo,Costcenter,Fraction) VALUES (1,'2021-05-01','2021-07-31',200,60), (1,'2021-05-01','2021-07-31',300,40), (1,'2022-01-01','2022-06-30',100,70), (1,'2022-01-01','2022-06-30',300,30), (1,'2022-07-01','2023-12-31',100,55), (1,'2022-07-01','2023-12-31',200,45); GO **Desired output** PN Costcenter Fraction ValidFrom ValidTo -- ---------- -------- ---------- ---------- 1 100 100 2020-10-01 2020-12-31 CC 100 no split 1 200 100 2021-01-01 2021-04-30 CC 200 no split 1 300 40 2021-05-01 2021-07-31 CC 300 40% and 1 200 60 2021-05-01 2021-07-31 60% CC 200 1 200 100 2021-08-01 2021-12-31 CC 200 no split 1 100 70 2022-01-01 2022-06-30 CC 100 70% and 1 300 30 2022-01-01 2022-06-30 30% CC 300 1 100 55 2022-07-01 2022-09-30 CC 100 55% and 1 200 45 2022-07-01 2022-09-30 45% CC 200 1 100 55 2023-01-01 2023-03-31 CC 100 55% and 1 200 45 2023-01-01 2023-03-31 45% CC 200 From 2022-10-01 until 2022-12-31 is no CC, although there is a fraction. The case is similar to [this one](https://dba.stackexchange.com/questions/282604/split-intervals) , but it goes a little further when a cost center allocation is within a longer period of time. **Technical background** As requested, a bit more background. To explain the background in more detail. The tables come from a personnel management software. There are actually even more tables involved in this problem, but I wanted to reduce the example to a minimum. Each employee is assigned to at least one cost center. This should be represented with the CCmain table. There are employees who are assigned to several cost centers. The allocation of the employee is then entered in the CCfraction table. There are then at least two entries in the CCfraction table. One for the main cost center and one for the other cost center to which the employee is allocated. It is also possible for an employee to be allocated to three or more cost centers. In this case, there would be more entries. What I have removed in the reduction to this example is that there is another table between the CCmain and CCfraction tables. This table allows several employees to use the same cost center splitting. As in this example, it is possible that an employee does not have a master cost center, but the allocation continues to run because it can be valid for other employees. This is the technical background. Sorry that I have reduced the example to a minimum.

I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique. start, end are timestamps(and end can be NULL). Say that I want to eliminate overlapping intervals but if there's any, I want to keep the ones with the highest interval_id. For the interval elimination, I am using the following criteria:

(t1.start =t2.start OR t1.end IS NULL)

I am doing a (self) CROSS JOIN and have something like this already:

SELECT t1.object_id, t1.interval_id, t1.start, t1.stop
FROM data AS t1, data AS t2
WHERE t1.object_id = t2.object_id -- we want to compare same objects
AND (t1.start =t2.start OR t1.end IS NULL)) --overlapping condition
AND t1.interval_id > t2.interval_id --keep ones with the highest id?

My main question is if this is a correct approach to keep both non-overlapping intervals and overlapping intervals with the highest interval_id or am I doing anything wrong? I wish I could fully test out myself but I currently don't have access to the db, so any input would be appreciated.

Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here): Code Year Month MAT000000001 2022 1 MAT000000001 2022 2 MAT000000001 2022 3 MAT000000001 2022 4 MAT000000001 2022 5 MAT000000001 2022 6 MAT000000001 2022 7 MAT000000001 2022 8 MAT000000001 2022 9 MAT000000001 2022 10 MAT000000001 2022 11 MAT000000001 2022 12 MAT000000001 2023 1 MAT000000001 2023 2 MAT000000001 2023 3 MAT000000001 2023 4 MAT000000001 2023 5 MAT000000001 2023 6 MAT000000001 2023 7 MAT000000001 2023 8 MAT000000001 2023 9 MAT000000001 2023 10 MAT000000001 2023 11 MAT000000001 2023 12 MAT000000002 2022 1 MAT000000002 2022 2 MAT000000002 2022 3 MAT000000002 2022 4 Also, I've this kind of History table: CREATE TABLE [dbo].History( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Code] [varchar](12) NOT NULL, [DateStart] [datetime] NOT NULL, [DateEnd] [datetime] NOT NULL, CONSTRAINT [PK_ReferentiStorico] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO With data such as: Id Code DateStart DateEnd 1 MAT000000001 1990-01-01 2023-03-31 2 MAT000000002 1990-01-01 9999-12-31 3 MAT000000001 2023-04-01 9999-12-31 Now, what I need to do is join the first table with the History, with the same Code and (and this is the heavy part) year month within the period described with DateStart and DateEnd. In the example above, this must be the result: Code Year Month HistoryId MAT000000001 2022 1 1 MAT000000001 2022 2 1 MAT000000001 2022 3 1 MAT000000001 2022 4 1 MAT000000001 2022 5 1 MAT000000001 2022 6 1 MAT000000001 2022 7 1 MAT000000001 2022 8 1 MAT000000001 2022 9 1 MAT000000001 2022 10 1 MAT000000001 2022 11 1 MAT000000001 2022 12 1 MAT000000001 2023 1 1 MAT000000001 2023 2 1 MAT000000001 2023 3 1 MAT000000001 2023 4 3 MAT000000001 2023 5 3 MAT000000001 2023 6 3 MAT000000001 2023 7 3 MAT000000001 2023 8 3 MAT000000001 2023 9 3 MAT000000001 2023 10 3 MAT000000001 2023 11 3 MAT000000001 2023 12 3 MAT000000002 2022 1 2 MAT000000002 2022 2 2 MAT000000002 2022 3 2 MAT000000002 2022 4 2 Which the correct way to do this with SQL Server? Not sure about the performance of this (i.e. create Date for every row): inner join History as hist on ana.Code = hist.Code and datefromparts(initTable.Year, initTable.Month, 1) between hist.DateStart and hist.DateEnd

I have searched a bit and not come across anything comparable, so I'm asking this question here, hoping I haven't overlooked anything. In my situation, we store deliveries on the database, which contain their time interval from start of delivery to the end. This looks roughly like this:

create table delivery_master (
    id uuid primary key,
    order_id uuid not null,
    start_time timestamp with time zone not null,
    end_time timestamp with time zone not null,
    delivery_id uuid null,
    quantity numeric(7,2) not null,
    vehicle_id uuid null,
    center_id uuid not null,
    unique(delivery_id)
);

As vehicles are assigned to certain centers, we first check which vehicles are assigned to what center and then use that list of vehicle_ids to proceed. This part is a relatively easy lookup. Now, I get a new order, and I calculated how many deliveries they have and their start and end time. I now want to query the DB, to see which of the vehicles that are assigned, have _open spaces_ to fit these deliveries in. This has some additional problems: A vehicle can be assigned to multiple delivery centers (i.e. imagine that both are relatively close so they could in theory on their way pickup something from the second center). My current query looks like this:

select pdm.*
from delivery_master dm,
unnest(
    '{2023-05-04T08:00:00Z, 2023-05-04T09:07:00Z}'::timestamptz[],'{2023-05-04T10:00:00Z, 2023-05-04T12:00:00Z}'::timestamptz[]
    ) periods(stime, etime)
where dm.vehicle_id in ('0a2c7f64-f465-4501-8a98-6ebb255dcf98', '87bc94dc-4da8-4f47-9b47-a79257997ce0')
	and not (dm.start_time,dm.end_time) OVERLAPS (periods.stime, periods.etime)
	and date(dm.start_time) = '2023-05-04';

Now, in my case I have 2 vehicles that are assigned to the center that should deliver the order. With that list, I now also supply a list of all start_time and end_time of the predictions I just made. My goal is to check, for all vehicles, if any have timeslots not overlapping said predictions. In that case, I want it to return all the rows of said vehicles; so that I can do manual assignment on the backend. My problem now is, that the returned rows _only_ contain the rows _that don't overlap_. In my case I would want, that if any of the "viable" rows doesn't overlap, to return all rows. EDIT: After a request, this would be an example dataset for it. The current query would only return a part of this dataset, but the goal would be for it to return all of them.

insert into delivery_master values
('cb96ca55-8d00-43da-a015-fd1f3a8283d2','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 07:33:00+00','2023-05-04 09:06:00+00','d5d6a4ce-d552-444b-b983-b299b5a321da',8.00,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('cb96ca55-8d00-43da-a015-fd1f3a8283d2','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 07:33:00+00','2023-05-04 09:06:00+00','d5d6a4ce-d552-444b-b983-b299b5a321da',8.00,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('8e3f8cd6-7a11-4759-887e-e6970d3381b6','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 06:21:00+00','2023-05-04 07:52:00+00','3cfc2389-1247-47cf-86bb-d1741f673cda',8.00,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('8e3f8cd6-7a11-4759-887e-e6970d3381b6','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 06:21:00+00','2023-05-04 07:52:00+00','3cfc2389-1247-47cf-86bb-d1741f673cda',8.00,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('5273b249-58ee-4636-af7a-dadd3575bd86','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 08:39:00+00','2023-05-04 10:12:00+00','4792cc4d-875e-421c-8f5c-b5d46f17c604',2.50,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('5273b249-58ee-4636-af7a-dadd3575bd86','f00fb9dc-b212-4be3-a936-df1f3420b215','2023-05-04 08:39:00+00','2023-05-04 10:12:00+00','4792cc4d-875e-421c-8f5c-b5d46f17c604',2.50,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('80322214-66c9-46dd-88f5-d949ffa58c89','230c9ea8-60fe-4472-894a-7fe866a0383c','2023-05-04 07:25:00+00','2023-05-04 09:38:00+00','cc76622d-f9ed-4f25-b5cb-bf8ad01c29f6',7.50,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('80322214-66c9-46dd-88f5-d949ffa58c89','230c9ea8-60fe-4472-894a-7fe866a0383c','2023-05-04 07:25:00+00','2023-05-04 09:38:00+00','cc76622d-f9ed-4f25-b5cb-bf8ad01c29f6',7.50,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('db7e0c5a-9b54-4133-939e-5bed5487943e','6b0bac44-ded2-41d8-b581-7c798f52a22f','2023-05-04 10:25:00+00','2023-05-04 11:32:00+00','a1e66278-cc3d-4f9b-bd80-913628d5a7bd',2.00,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('db7e0c5a-9b54-4133-939e-5bed5487943e','6b0bac44-ded2-41d8-b581-7c798f52a22f','2023-05-04 10:25:00+00','2023-05-04 11:32:00+00','a1e66278-cc3d-4f9b-bd80-913628d5a7bd',2.00,'87bc94dc-4da8-4f47-9b47-a79257997ce0','2dfa4013-a312-4247-9f29-3e21631ace6b'),
('defa7fff-77b5-4291-99d3-2ac39abb3b69','48e2a591-7b34-4291-91f0-955a583f6e97','2023-05-04 16:30:33+00','2023-05-04 19:30:33+00',null,7.00,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','9f14bbb3-279b-4e7a-8082-8bb89cfa0b3d'),
('defa7fff-77b5-4291-99d3-2ac39abb3b69','48e2a591-7b34-4291-91f0-955a583f6e97','2023-05-04 16:30:33+00','2023-05-04 19:30:33+00',null,7.00,'0a2c7f64-f465-4501-8a98-6ebb255dcf98','9f14bbb3-279b-4e7a-8082-8bb89cfa0b3d');

I have a huge table in Postgresql-11 like following: CREATE TABLE my_huge_table( tick_time timestamp(6) with time zone NOT NULL, brok_time timestamp(6) with time zone, trade_day date NOT NULL, --other fields ... ... CONSTRAINT my_huge_table_pkey PRIMARY KEY (tick_time) ); CREATE INDEX idx_my_huge_table_td_time ON my_huge_table USING brin ( trade_day, abs(tick_time - brok_time) ); Then I make a query and want it to take advantage of the index idx_my_huge_table_td_time, like this: SELECT * FROM my_huge_table WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') AND ABS(tick_time - brok_time) ERROR: function abs(interval) does not exist > > LINE 3: AND ABS(tick_time - brok_time) > ^ > > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > SQL state: > 42883 Character: 525 It looks like that the func abs() can **NOT** accept a interval value as a argument. Then, I changed my query: SELECT * FROM my_huge_table WHERE trade_day BETWEEN TO_DATE('20220104', 'YYYYMMDD') AND TO_DATE('20220104', 'YYYYMMDD') AND GREATEST(tick_time - brok_time, brok_time - tick_time) < INTERVAL '10 s'; This time it can be executed, but didn't take advantage of the index. My questions: 1.How should I compose the expression of index? In fact I want it to record a distance(absolute interval value) between two timestamp fields; 2.How should I code the query that can use the index above? 3.In fact GREATEST(tick_time - brok_time, brok_time - tick_time) is **NOT** a good idea, since it invoked two times computing. Isn't it? 4.After created the index, I note that the real DDL SQL of the index reported by PostgreSQL is: CREATE INDEX idx_my_huge_table_td_time ON public.my_huge_table USING brin (trade_day, abs(date_part('epoch'::text, tick_time - brok_time))); Have the value of the expresstion casted into a text type? It apparently is **NOT** my expectation!

I'm designing a postgres table to record the intervals contained in a weekly schedule. It would hold schedules for multiple businesses, and a simple example dataset might look like: business_id interval ----------- ----------------------------------- 1 Sunday 10:00:00 – Sunday 14:00:00 1 Sunday 22:00:00 – Monday 02:00:00 1 Friday 11:00:00 – Friday 16:00:00 1 Saturday 15:00:00 – Sunday 01:00:00 Note that intervals can cross the boundaries between days. A business should not have overlapping intervals, and I'd like to design the table in a way that lets me enforce this. I was considering mapping these day-of-week + time-of-day values to the corresponding numbers of seconds since the beginning of the week, storing intervals as [int4range](https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-BUILTIN) and using an exclusion constraint to prohibit overlapping integer ranges, but that wouldn't properly address intervals that wrap around the end of the week. Is there a good way to model this kind of cyclical data and prohibit overlaps?

I have two tables. Each holds some attributes for a business entity and the date range for which those attributes were valid. I want to combine these tables into one, matching rows on the common business key and splitting the time ranges. The real-world example is two source temporal tables feeding a type-2 dimension table in the data warehouse. The entity can be present in neither, one or both of the source systems at any point in time. Once an entity is recorded in a source system the intervals are well-behaved - no gaps, duplicates or other monkey business. Membership in the sources can end at different dates. The business rules state we only want to return intervals where the entity is present in both sources simultaneously. What query will give this result? This illustrates the situation:

Month          J     F     M     A     M     J     J
Source A:  
Source B:            
               
Result:

Sample Data ----------- For simplicity I've used closed date intervals; likely any solution could be extended to half-open intervals with a little typing.

drop table if exists dbo.SourceA;
drop table if exists dbo.SourceB;
go

create table dbo.SourceA
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);

create table dbo.SourceB
(
    BusinessKey int,
    StartDate   date,
    EndDate     date,
    Attribute   char(9)
);
GO


insert dbo.SourceA(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990101', '19990113', 'black'),
    (1, '19990114', '19990313', 'red'),
    (1, '19990314', '19990513', 'blue'),
    (1, '19990514', '19990613', 'green'),
    (2, '20110714', '20110913', 'pink'),
    (2, '20110914', '20111113', 'white'),
    (2, '20111114', '20111213', 'gray');

insert dbo.SourceB(BusinessKey, StartDate, EndDate, Attribute)
values
    (1, '19990214', '19990313', 'left'),
    (1, '19990314', '19990413', 'right'),
    (1, '19990414', '19990713', 'centre'),
    (1, '19990714', '19990730', 'back'),
    (2, '20110814', '20110913', 'top'),
    (2, '20110914', '20111013', 'middle'),
    (2, '20111014', '20120113', 'bottom');

Desired output --------------

BusinessKey StartDate   EndDate     a_Colour  b_Placement
----------- ----------  ----------  --------- -----------
1           1999-02-14  1999-03-13  red       left     
1           1999-03-14  1999-04-13  blue      right    
1           1999-04-14  1999-05-13  blue      centre   
1           1999-05-14  1999-06-13  green     centre   
2           2011-08-14  2011-09-13  pink      top      
2           2011-09-14  2011-10-13  white     middle   
2           2011-10-14  2011-11-13  white     bottom   
2           2011-11-14  2011-12-13  gray      bottom

I have an issue that I want to count total data with 1 hour interval. However start time is 7:30 Time Status 2022-03-24 07:36:00, Fail 2022-03-24 07:59:00, Pass 2022-03-24 09:32:00, Pass 2022-03-24 09:41:00, Pass 2022-03-24 10:02:00, Fail 2022-03-24 11:02:00, Pass 2022-03-24 11:22:00, Fail You can see that before 8:30 I have 2 data that 1 pass(07:59) and 1 fail(07:36) so I can count it and create data below Time Status Total 2022-03-24 08:30 Pass 1 2022-03-24 08:30 Fail 1 2022-03-24 09:30 Pass 0 2022-03-24 09:30 Fail 0 2022-03-24 10:30 Pass 2 2022-03-24 10:30 Fail 1 2022-03-24 11:30 Pass 1 2022-03-24 11:30 Fail 1

I often run into the following problem. I have two tables of intervals. They are bounded by dates (with no time component). Within each table the intervals do not overlap. StartTs | EndTs --|-- 2015-01-03 | 2015-03-02 2015-03-05 | 2015-04-01 StartTs | EndTs --|-- 2015-01-07 | 2015-02-27 2015-03-01 | 2015-03-13 2016-01-01 | 2016-01-02 And I want to find the set difference of the the two tables, i.e., the intervals representing the time in the first table not in the second table. Desired output for the dummy example above: StartTs | EndTs --|-- 2015-01-03 | 2015-01-06 2015-02-28 | 2015-02-28 2015-03-14 | 2015-04-01 i.e. if the first table's dates are marked in yellow below and the ranges from the second table surrounded with a box I'd be looking for the contiguous ranges of unboxed yellow dates. I'm currently treating intervals as inclusive on both ends, and using DateTime for my timestamps. My current approach is to take the complement of the second table via a triple self-join (yuck) and then intersecting the result with the first table via a join. Not fun. Is there a better approach?

I have a table which stores a list of activities with an interval of time delimited by 2 dates. Sample: +------+---------------------+---------------------+-------------+ | name | start | end | time (calc) | +------+---------------------+---------------------+-------------+ | me | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 | 30 | | me | 2017-04-03 23:45:00 | 2017-04-04 00:15:00 | 30 | | me | 2017-04-04 10:00:00 | 2017-04-04 11:00:00 | 60 | | me | 2017-04-04 10:30:00 | 2017-04-04 11:30:00 | 60 | | me | 2017-04-05 23:00:00 | 2017-04-05 23:30:00 | 30 | | me | 2017-04-05 23:15:00 | 2017-04-07 00:45:00 | 1530 | +------+---------------------+---------------------+-------------+ I would like to know how many minutes are occupied each day per user (and then per week), so I need to transform current table into where intervals which share a partial space time are merged in a single one, and intervals which are in several days are split, like the next one: +------+---------------------+---------------------+-------------+ | name | start | end | time (calc) | +------+---------------------+---------------------+-------------+ | me | 2017-04-03 11:00:00 | 2017-04-03 11:30:00 | 30 | | me | 2017-04-03 23:45:00 | 2017-04-03 23:59:59 | 15 | | me | 2017-04-04 00:00:00 | 2017-04-04 00:15:00 | 15 | | me | 2017-04-04 10:00:00 | 2017-04-04 11:30:00 | 90 | | me | 2017-04-05 23:00:00 | 2017-04-05 23:59:59 | 60 | | me | 2017-04-06 00:00:00 | 2017-04-06 23:59:59 | 1440 | | me | 2017-04-07 00:00:00 | 2017-04-07 00:45:00 | 45 | +------+---------------------+---------------------+-------------+ To then query it easily in order to obtain minutes per day: +------+------------+------+ | name | day | time | +------+------------+------+ | me | 2017-04-03 | 45 | | me | 2017-04-04 | 105 | | me | 2017-04-05 | 60 | | me | 2017-04-06 | 1440 | | me | 2017-04-07 | 45 | +------+------------+------+ I were looking for information an I found how to merge multiple date intervals here (https://dba.stackexchange.com/questions/80789) , however I am not able to split an interval in several days. It is possible to do it in a single query? How could I do it? **Edit:** SQL (structure and data): CREATE TABLE activities ( id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, name VARCHAR(45), start DATETIME, end DATETIME, time INT GENERATED ALWAYS AS (TIMESTAMPDIFF(MINUTE, start, end)) VIRTUAL ); INSERT INTO activities (name, start, end) VALUES ('me','2017-04-03 11:00','2017-04-03 11:30'), ('me','2017-04-03 23:45','2017-04-04 00:15'), ('me','2017-04-04 10:00','2017-04-04 11:00'), ('me','2017-04-04 10:30','2017-04-04 11:30'), ('me','2017-04-05 23:00','2017-04-05 23:30'), ('me','2017-04-05 23:15','2017-04-07 00:45'); SQL to merge multiple intervals (Soruce: https://dba.stackexchange.com/questions/80789) : SELECT name, min(start) AS start, end, TIMESTAMPDIFF(MINUTE, MIN(start), end) AS time FROM ( SELECT x.name, x.start, min(y.end) AS end FROM activities AS x JOIN activities AS y ON x.name = y.name AND x.start = z.start AND y.end u.start AND x.start <= u.start ) GROUP BY x.name, x.start ) AS v GROUP BY name, end;

I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's going to break a bunch of existing functionality so what I am trying to do is cast that varchar into an interval in the select statement. I can't figure out how to do this, I was thinking I first need to format it into a timestamp then turn that into an interval? But haven't been able to find any info about this specific case.Thanks for the help! Edit 1: a_horse_with_no_name's solution worked but I was getting an error previously because of the empty rows so I wrote a case like so to fix it

case
  when
    duration = ''
  then
    interval '0'
  else
    duration::interval
end

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. ### Sleep Table Here is the select statement I have so far:

select wakeuptime,hour from
		(
			select patientid, 
                   sum(s.duration) as hour, 
                   Date(s.wake_up_time) as wakeuptime 
            from sleep s
			where patientid = pid::varchar
			group by patientid, Date(s.wake_up_time) 
			order by Date(s.wake_up_time) desc
		) s
		order by s.wakeuptime asc;

I need to calculate Sum (wake_up_time - time_to_bed) where it should convert minutes into hours for example 55mins + 15mins should return 1hr 10mins.

I have this table in PostgreSQL 13: CREATE TABLE public."domain" ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, domain_name varchar NOT NULL, -- more columns expire_date timestamp NULL, days_before_trigger int4 NOT NULL DEFAULT 14 ); Now I want to add a generated column notify_trigger_date, derived from expire_date minus days_before_trigger, to record my website url ssl certificate expiry date. How to auto-generate that column? It can look like this: notify_trigger_date = expire_date - 7 day I am trying to implement it like this: ALTER TABLE "domain" ADD COLUMN notify_trigger_date timestamp GENERATED ALWAYS AS ((expire_date::timestamp - '1 day')) STORED; I do not know how to replace the 1 day with the number of days from days_before_trigger? This command runs with error: > SQL Error : ERROR: invalid input syntax for type timestamp: "1 day" What should I do to make it work? I have read the PostgreSQL documentation but found no clear solution for this.

I have a data source with the time information being formatted like this: 'MI:SS'. I am trying to coerce that to a native postgres INTERVAL or TIME type so I can leverage the equivalent operators. I have tried the following:

CASE
            WHEN (event."BIDASK" = 'BP') THEN '10:00'
            WHEN (event."MARKERTIME" = '00:-1') OR (event."MARKERTIME" = '') THEN '00:00'
            ELSE event."MARKERTIME"
        END ::interval minute to second

event."MARKERTIME" is a "MM:SS" formatted string. I note however that it gets converted to 'HH:MM' in the casted interval type. ie. for the string "10:00" - I get: hours 10, minutes 00, seconds 00. - I would expect: minutes 10, seconds 00 I am working on postgres 13. Here is a db-fiddle illustrating this casting behaviour: https://www.db-fiddle.com/f/2QqG36bKnjAyjqecBKpDU/2 EDIT: One trick I find it working is to prepend the string with '00:' but I am hoping for a better way to achieve that because I cannot rely on the fact that the MI part of the string will be bounded between 0 and 59.

I have data stored in a table like this: CREATE TABLE Records ( [Id] int IDENTITY(1,1) not null ,[From] datetime not null ,[To] datetime not null ,[Priority] int not null ) Every row contains time record with from-to range and its priority. I have following data: | Id | From | To | Priority | |----|----------------------|----------------------|----------| | 1 | 2021-01-04T00:00:00Z | 2021-01-04T23:59:59Z | 0 | | 2 | 2021-01-04T08:00:00Z | 2021-01-04T16:30:00Z | 1 | | 3 | 2021-01-05T00:00:00Z | 2021-01-05T23:59:59Z | 0 | | 4 | 2021-01-05T08:00:00Z | 2021-01-05T16:30:00Z | 1 | | 5 | 2021-01-05T16:30:00Z | 2021-01-05T17:30:00Z | 100 | | 6 | 2021-01-05T17:30:00Z | 2021-01-05T23:00:00Z | 100 | | 7 | 2021-01-05T23:00:00Z | 2021-01-05T23:59:59Z | 100 | | 8 | 2021-01-06T00:00:00Z | 2021-01-06T23:59:59Z | 0 | | 9 | 2021-01-06T08:00:00Z | 2021-01-06T10:00:00Z | 100 | | 10 | 2021-01-06T08:00:00Z | 2021-01-06T16:30:00Z | 1 | | 11 | 2021-01-06T10:00:00Z | 2021-01-06T12:30:00Z | 100 | | 12 | 2021-01-06T12:30:00Z | 2021-01-06T14:30:00Z | 100 | | 13 | 2021-01-06T14:30:00Z | 2021-01-06T15:30:00Z | 100 | | 14 | 2021-01-06T17:00:00Z | 2021-01-06T17:45:00Z | 100 | | 15 | 2021-01-06T17:45:00Z | 2021-01-06T19:45:00Z | 100 | | 16 | 2021-01-06T19:45:00Z | 2021-01-06T20:30:00Z | 100 | | 17 | 2021-01-07T00:00:00Z | 2021-01-07T23:59:59Z | 0 | | 18 | 2021-01-07T08:00:00Z | 2021-01-07T16:30:00Z | 1 | | 19 | 2021-01-07T08:30:00Z | 2021-01-07T11:30:00Z | 100 | | 20 | 2021-01-07T11:30:00Z | 2021-01-07T12:30:00Z | 100 | | 21 | 2021-01-07T12:30:00Z | 2021-01-07T15:30:00Z | 100 | It can be seen that there are records where from-to overlaps with other records. Records with bigger priority should overwrite records with smaller one. Expected results should be: | Id | From | To | Priority | |----|----------------------|----------------------|----------| | 1 | 2021-01-04T00:00:00Z | 2021-01-04T08:00:00Z | 0 | | 2 | 2021-01-04T08:00:00Z | 2021-01-04T16:30:00Z | 1 | | 1 | 2021-01-04T16:30:00Z | 2021-01-04T23:59:59Z | 0 | | 3 | 2021-01-05T00:00:00Z | 2021-01-05T08:00:00Z | 0 | | 4 | 2021-01-05T08:00:00Z | 2021-01-05T16:30:00Z | 1 | | 5 | 2021-01-05T16:30:00Z | 2021-01-05T17:30:00Z | 100 | | 6 | 2021-01-05T17:30:00Z | 2021-01-05T23:00:00Z | 100 | | 7 | 2021-01-05T23:00:00Z | 2021-01-05T23:59:59Z | 100 | | 8 | 2021-01-06T00:00:00Z | 2021-01-06T08:00:00Z | 0 | | 9 | 2021-01-06T08:00:00Z | 2021-01-06T10:00:00Z | 100 | | 11 | 2021-01-06T10:00:00Z | 2021-01-06T12:30:00Z | 100 | | 12 | 2021-01-06T12:30:00Z | 2021-01-06T14:30:00Z | 100 | | 13 | 2021-01-06T14:30:00Z | 2021-01-06T15:30:00Z | 100 | | 10 | 2021-01-06T15:30:00Z | 2021-01-06T16:30:00Z | 1 | | 8 | 2021-01-06T16:30:00Z | 2021-01-06T17:00:00Z | 0 | | 14 | 2021-01-06T17:00:00Z | 2021-01-06T17:45:00Z | 100 | | 15 | 2021-01-06T17:45:00Z | 2021-01-06T19:45:00Z | 100 | | 16 | 2021-01-06T19:45:00Z | 2021-01-06T20:30:00Z | 100 | | 8 | 2021-01-06T20:30:00Z | 2021-01-06T23:59:59Z | 0 | | 17 | 2021-01-07T00:00:00Z | 2021-01-07T08:00:00Z | 0 | | 18 | 2021-01-07T08:00:00Z | 2021-01-07T08:30:00Z | 1 | | 19 | 2021-01-07T08:30:00Z | 2021-01-07T11:30:00Z | 100 | | 20 | 2021-01-07T11:30:00Z | 2021-01-07T12:30:00Z | 100 | | 21 | 2021-01-07T12:30:00Z | 2021-01-07T15:30:00Z | 100 | | 18 | 2021-01-07T15:30:00Z | 2021-01-07T16:30:00Z | 1 | | 17 | 2021-01-07T16:30:00Z | 2021-01-07T23:59:59Z | 0 | Possible way how to solve it I have found on [reddit](https://www.reddit.com/r/SQL/comments/6gsoj7/prioritising_overlapping_time_ranges_to_produce_a/ditsyfe) , but it does not work on my dataset as you can see on [SQL Fiddle Demo](http://sqlfiddle.com/#!18/e7a1f/7) (with small change - on reddit lower priority beats higher priority, I need: higher priority beats lower priority). Basically:

0:      ++++++++++++++++++++++++
1:              ----------       
100:                  |||||||
result: ++++++++------|||||||+++

Any suggestion/solution on how to solve it on SQL Server 2012? I thought that it is common problem, however I was not able to find a solution on the internet yet. --- Just to clarify my problem, I have overlapping events like this:

+-----------------------------------------------------------------------+
| #ID:1#                       00:00-23:59                              |
+-----------------------------------------------------------------------+

                     +-----------------------------+
                     | #ID:2#  08:00-16:30         |
                     +-----------------------------+

I need to get this (because ID2 has higher priority than ID1):

+--------------------+-----------------------------+--------------------+
| #ID:1# 00:00-08:00 | #ID:2# 08:00-16:30          | #ID:1# 16:30-23:59 |
+--------------------+-----------------------------+--------------------+

If ID2 had lower priority than ID1, it would be like this:

+-----------------------------------------------------------------------+
| #ID:1#                       00:00-23:59                              |
+-----------------------------------------------------------------------+

There can be many events in the same time with different priorities.

The context is connecting to a Postgres db from a rest server. To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is older/newer than an arbitrary value. In the example query below, the table structure is simple - name is of type text, and creation_date is of type timestamp. So when I do something like server_pg_module:query("select name from new_table where current_timestamp - creation_date >,file => >, line => >, message => >, routine => >,severity => >, {unknown,86} => >}}} And when I pass '2 days' as the parameter, it throws a badarg error.

I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on minutes.

CREATE OR REPLACE FUNCTION time_diff(hr INT, min INT, sec INT, exp_hr INT)
RETURNS INT
LANGUAGE plpgsql AS
$$
DECLARE
    cur_time    TIME;
    expire_time TIME;

    diff_interval INTERVAL;
    diff INT = 0;
BEGIN
    cur_time    = CONCAT(hr,  ':',  min, ':', sec) AS TIME; -- cast hour, minutes and seconds to TIME
    expire_time = CONCAT(exp_hr, ':00:00') AS TIME;         -- cast expire hour to TIME

    -- MINUS operator for TIME returns interval 'HH:MI:SS;
    diff_interval = expire_time - cur_time;

    diff = DATE_PART('hour', diff_interval);

    diff = diff * 60 + DATE_PART('minute', diff_interval);

    --diff = diff * 60 + DATE_PART('second', diff_interval);

    RETURN diff;
END;
$$;

Example: 01:15:00 - 02:00:00 should give me 45 minutes, so I do the following and I get the correct answer.

select * from time_diff(1, 15, 0, 2);

However, if I do this: 23:15:00 - 01:00:00 - the should give me 105 minutes (60 + 45).

select * from time_diff(23, 15, 0, 1);

But the result I am getting is -1335. I am trying to work out where I have gone wrong here. Also I am invoking DATE_PART functions, this seems to be a quite an expensive process in terms of CPU usage. Is there a better way of optimising this function. With the first example I am getting results in 0.007s on 2018 i7 Mac mini. Although I do think this function is quick, but could it be better?