MySQL update query against a certain number of consecutive rows
this is an example of initial table data: | id | reservation | | -------- | ----------- | | 1 | 15 | | 2 | 0 | | 3 | 0 | | 4 | 16 | | 5…
Counting consecutive blocks in SQL – Mysql
I want to read a table with a SQL query in MySQL, which gives me the consecutive blocks of a row. The query may only last a maximum of 120 seconds, which I already exceed with my queries, I also…
How do I convert a JOIN on the same table into a Group By – Mysql
I have a table with records in it. The desire was to find the records that were exactly the same, except for the time of the 2 records have a difference of less than 2 seconds. I created a query…
SQL: Merge almost identical lines with start_date and end_date – Postgresql
I have the following rows: id status startdate enddate 111 0 2023-01-13 2023-01-17 111 1 2023-01-18 2023-01-22 111 1 2023-01-23 2023-01-23 111 1 2023-01-24 2023-01-26 111 0 2023-01-27 9999-12-31 222 0 2023-01-19 2023-01-22 222 0 2023-01-23 2023-01-27 222 0 2023-01-28…
Postgresql – Find start date and end date for continuous status
here is my dataset processed_on|status | ------------+-------+ 2023-01-01 |Success| 2023-01-02 |Success| 2023-01-03 |Success| 2023-01-04 |Fail | 2023-01-05 |Fail | 2023-01-06 |Success| 2023-01-07 |Fail | 2023-01-08 |Success| 2023-01-09 |Success| expected output is -------------------------- start_date|end_date|status -------------------------- 2023-01-01|2023-01-03|Success 2023-01-04|2023-01-05|Fail 2023-01-06|2023-01-06|Success 2023-01-07|023-01-07|Fail 2023-01-08|2023-01-09|Success i…
Current and previous days date diff in days with some condition – Postgresql
I have the first three fields of the following table. I want to compute the number of consecutive days an amount was higher than 0 ("days" field). key date amount days 1 2023-01-23 0 0 1 2023-01-22 10 2 1…
Gaps and island combined with a recursion – Postgresql
I have a gaps and islands SQL problem combined with a recursion. Background: I run a PostgreSQL database with school vacations and bank holidays from which I render calendar views for schools, city and what not. I code a lot…
Fill gaps in preceeding rows – Postgresql
I'm working with the expression builder of Palantir Contour. The following table is given, and I need to fill the gaps in the counter column. Problem: The rows before the first value shall be filled with first counter -1 Date…
Sum over a given time period – Postgresql
The following codes gives the total duration that a light has been switched on. CREATE TABLE switch_times ( id SERIAL PRIMARY KEY, is1 BOOLEAN, id_dec INTEGER, label TEXT, ts TIMESTAMP WITH TIME ZONE default current_timestamp ); CREATE VIEW makecount AS…
Postgresql – Basing OFFSET on another query?
My table structure is roughly as described in this post: name processed processing updated ref_time abc t f 27794395 27794160 def t f 27794395 27793440 ghi t f 27794395 27793440 jkl f f 27794395 27794160 mno t f 27794395 27793440…