Linked Questions
17 questions linked to/from Find overlapping date ranges in PostgreSQL
21
votes
2
answers
77k
views
Subtract hours from the now() function
We have a machine running 24x7. Every day I report the number of pieces it produced per hour. In our case one working day means '2015-06-16 06:00:00' to '2015-06-17 06:00:00' for example.
Here is my ...
- 575
3
votes
2
answers
24k
views
PostgreSQL function with select returning one value
I have a table years (jan 1st of each ear). I want to create a function that takes each of those years and runs a query on another table to count the number of rows that belonging to that year. I ...
- 2,541
4
votes
2
answers
14k
views
Optimize BETWEEN date statement
I need help optimizing a Postgres query which uses the BETWEEN clause with a timestamp field.
I have 2 tables:
ONE(int id_one(PK), datetime cut_time, int f1 ...)
containing about 3394 rows
TWO(int ...
- 371
6
votes
1
answer
4k
views
Non-overlap, continuous timestamp ranges (tstzrange) for opening hours
CREATE TABLE operating_period (
id SERIAL NOT NULL PRIMARY KEY,
during TSTZRANGE NOT NULL,
-- other meta fields
);
Requirements:
1. No operating period can overlap with each other
Question:
How ...
- 21k
6
votes
2
answers
7k
views
Count max. number of concurrent user sessions per day
Situation
We have a PostgreSQL 8.4 database containing user sessions with login date/time and logout date/time per row. Our web application records this time and also handles the case when user does ...
- 63
5
votes
3
answers
2k
views
Average stock history table
I have a table that tracks changes in stocks through time for some stores and products. The value is the absolute stock, but we only insert a new row when a change in stock occurs. This design was to ...
- 474
2
votes
2
answers
5k
views
Left outer join acting like inner join
Summary
My goal is to find every user who has ever been assigned to a task, and then generate some statistics over a particular date range, and associate the stats with the original set of users. When ...
- 304k
1
vote
3
answers
2k
views
SQL OVERLAPS how to include the dates of at the extremes?
How to include in an SQL OVERLAPS the start and the end date?
Like <= >=. How to?
Because it seeems to me that OVERLAPS check only the range in between, but for example:
Date 01 Jan 2001 - 05 ...
user4412054
0
votes
3
answers
1k
views
Choose active employes per month with dates formatted dd/mm/yyyy
I'm having a hard time explaining this through writing, so please be patient.
I'm making this project in which I have to choose a month and a year to know all the active employees during that month ...
- 2,631
0
votes
2
answers
3k
views
How to apply time slices to an existing SQL query
I have a table with approx 8 million rows that I need time slice data from.
I'm using PostgreSQL 9.1.
I need to query this table for a sum of the max(start_time) associated 'data' values for each '...
- 45
1
vote
2
answers
2k
views
Many to Many Table - Performance is bad
The following tables are given:
--- player --
id serial
name VARCHAR(100)
birthday DATE
country VARCHAR(3)
PRIMARY KEY id
--- club ---
id SERIAL
name VARCHAR(100)
country VARCHAR(3)
PRIMARY KEY id
-...
- 1,146
2
votes
2
answers
616
views
Extract number of days per week from multiple date ranges
I have a table trips in PostgreSQL 10.5:
id start_date end_date
----------------------------
1 02/01/2019 02/03/2019
2 02/02/2019 02/03/2019
3 02/06/2019 02/07/2019
4 02/06/2019 ...
- 2,414
1
vote
1
answer
319
views
How to lookup based on ranged values
I have a table like:
id name
001to005 ABC
006to210 PQR
211to300 XYZ
This is not the final table i can make it any how i want...so i would like to lookup on this data on id and ...
- 41
2
votes
1
answer
505
views
postgresql trigger for insert
Now the trigger I write has the following problem:
if the new row I insert is conflict with one entry in the table weeklymeeting, it should not insert into table and give me error message. While if ...
- 245
-1
votes
2
answers
332
views
postgresql max(count(*)) - php
I have a problem in postgresql.
I have one cohorte (gathering of people) and i would like counting the persons in this cohorte.
Begin date : "2014-09-01", End date : "2014-11-30".
I have 5 persons ...
