Questions tagged [range-types]
The range-types tag has no usage guidance.
44
questions
2
votes
2
answers
62
views
Check if a value is within a numrange in PostgreSQL
In PostgreSQL 16, I need to check if a value is within a numrange stored in a table.
I found the following example with an int4range
SELECT 14 <@ (SELECT my_range
FROM my_table
WHERE id = 1);
But ...
- 371
1
vote
1
answer
187
views
Check int4range includes number using B-tree index
I have a table with two important columns: value and m_range, where value:
CREATE TABLE m_filter (
value BIGINT NOT NULL,
m_range ...
- 111
1
vote
1
answer
224
views
generate series of time ranges
I know we can use Postgres's generate_series() function to produce a list of timestamps. Can we do the same to produce a list of time ranges directly without having to manually convert generated ...
- 145
4
votes
1
answer
1k
views
Index not used for date range condition
I was querying like this:
SELECT count(*)
FROM orders
WHERE planned_shipping_date >= '2022-04-04'
AND planned_shipping_date < '2022-04-05'
Then I came across this answer and, because in more ...
- 547
1
vote
0
answers
167
views
Returning overlapping rows with same state based on Id
I have a table with internal_id, states, start time and end time. I am trying to overlay the recent state to the previous states and return the value.
I have a simple working example here which works ...
- 11
4
votes
1
answer
258
views
How to get index scan for OR'ed time-range predicates?
I have table events with fields:
id
user_id
time_start
time_end
...
And have b-tree index on (time_start, time_end).
SELECT user_id
FROM events
WHERE ((time_start <= '2021-08-24T15:30:00+00:00' ...
- 175
2
votes
1
answer
1k
views
Inserting date range arrays daterange[]
I'm trying to insert the value of an attribute of a tuple, which would be an array of date ranges, i.e. a daterange[], in a previous tuple accepted the value:
{"[2013-05-08,2018-03-22)"}
...
- 57
1
vote
1
answer
767
views
Counting working days in a month
I wrote this query that suppose to count the working days in a month but I get the following error:
[42883] ERROR: operator does not exist: double precision + interval Hinweis: No operator matches ...
- 11
8
votes
2
answers
3k
views
How to get non-overlapping distinct intervals from a PostgreSQL table?
Using postgresql 9.6.
The table has user sessions and I need distinct non overlapping sessions printed.
CREATE TABLE SESSIONS(
id serial NOT NULL PRIMARY KEY,
ctn INT NOT NULL,...
- 465
2
votes
1
answer
336
views
Time series index range contains operator (reverse)
Hi I am trying to optimise the a timestamp range contains <@ query for Postgres 12
I have done a bit of reading postgres documentation and discovered only GiST and SP-GiST indexes support this ...
- 252
1
vote
1
answer
79
views
How to create date ranges of events coming from an API where open state represents the date range and close state the gaps between the ranges?
I would like to store date/time ranges in PostgreSQL of some arbitrary events that have a state of open or closed and
a date/time of when the state changed.
Events coming from an API will have the ...
- 121
1
vote
2
answers
838
views
PostgreSQL multiple range best practice
I'm using tsrange in my project, but I don't know how to represent something.
I have a product that can be displayed on my website at some times, and can be hidden at other times. But tsrange has ...
- 11
0
votes
0
answers
40
views
DB scheme for the home exchange site: store date ranges or individual days for availability
I am creating a db schema for a home exchange site.
Host can set his home as exposed for exchange on certain dates. Guest must be able to view available dates.
If exposed date is free (not reserved ...
- 101
1
vote
1
answer
1k
views
How to use index for simple time range join?
I have a large (~ 100 million rows) timeseries table t_16 in Postgres 11.5 where the primary key is a field abs_date_time of type timestamp.
This is a follow up of this question:
Why does a CTE ...
- 233
1
vote
1
answer
151
views
How to search one bible verse from a source that may contain ranges?
My WordPress system contains posts with a field containing Bible references and ranges, eg.
John 5,1-10; Matthew 3,2; Mark 1,1-5
Users searching for a Bible references e.g. John 5,3 or Matthew 3,2 ...
- 23
7
votes
1
answer
14k
views
Creating index on TIMESTAMP column to use with range operators
TLDR: Can I create an index that's used by the following WHERE clause:
WHERE foo_date <@ tsrange('2018-01-01', '2018-02-01')
Let's say I have a table like this:
CREATE TABLE foo
(
foo_id ...
- 183
3
votes
3
answers
1k
views
How do I create a bounded range type in the SQL Server?
I want to create a bible verse table that has one field representing a range of bible verses. Currently I represent this range as pair of values (VerseBegId and VerseEndId). I want something like ...
- 33
1
vote
2
answers
484
views
How can I use Spatial Indexes for timestamps (non-spatial 1d data)?
I'm using MySQL 8.0. I have some table containing TIMESTAMP column and I need to select within TIMESTAMP range:
SELECT *
FROM t_tbl
WHERE ts BETWEEN @someDate1 AND @someDate2
I want to make this ...
- 121
2
votes
1
answer
2k
views
Excluding (or subtracting) multiple dateranges from a daterange in PostgreSQL
I have a list of items which contain: product_id, period and availability.
The link below references my SQLFiddle Example.
I want to get the availability periods of each product for a given period, e....
- 211
2
votes
2
answers
2k
views
What's the best way to use a gist index on tsrange to check if a time occurs after the range?
table (simplified)
Table "public.events"
Column | Type | Modifiers
-------------...
- 723
4
votes
2
answers
2k
views
How to check if integer array overlaps with int4range?
PostgreSQL version: 9.5
I am trying to write a query that will check if an array of specified decades overlaps in a range. For example, I'd like to find all of the people employed at a company in the ...
- 143
2
votes
1
answer
188
views
Ranges over domains do not trigger implicit casting
As far as I was able to find out, it is currently not possible in PostgreSQL v9.5 (and above?) to obtain implicit casting with custom range types. To illustrate,
when I define the following demo setup:...
- 317
3
votes
2
answers
1k
views
Extracting sum of time in a period
I have a login table, which registers datahora_ini (system entry time) and datahora_fim (system exit time). Sample data:
CREATE TABLE login(cod_user,datahora_ini,datahora_fim)
AS
VALUES
( 101::...
- 119
5
votes
1
answer
1k
views
Aggregate ranges by merging
There aren't any Aggregate functions for Range types in PostgreSQL.
How to aggregate ranges using a merge operation?
This solution works but does not look optimal:
CREATE TEMPORARY TABLE ...
- 253
7
votes
2
answers
8k
views
Determining if an IP is within an IPv4 CIDR Block
What is the fastest way to determine if an IP is contained within a CIDR block?
At the moment, whenever I store a CIDR address I also create two columns for starting and ending ip addresses. The ...
- 472
3
votes
6
answers
292
views
Standards for storing a rating as a range (1-5, 1 is best, etc.)
I am designing a ROAD_INSPECTION table that has a CONDITION field:
+---------+-----------+
| ROAD_ID | CONDITION |
+---------+-----------+
| 1 | 2 |
| 2 | 5 |
| 3 | ...
- 1,437
4
votes
1
answer
1k
views
Foreign key on PostgreSQL range bounds
Is it possible in PostgreSQL 9.6 to apply a foreign key constraint on the lower and upper bounds of a range (of integers in my case)?
Currently I keep the lower bound and upper bound in two columns, ...
- 353
5
votes
3
answers
4k
views
Element of an array in a daterange?
I have an array of dates, and I want to find out if any of the elements in the array appear within a given daterange.
For example (pseudocode - doesn't work, wrapped for readability):
SELECT ARRAY['...
- 175
0
votes
1
answer
26
views
Select data in steps and ranges
I have the following GPS data. I'm trying to get trips made by the user based on the engine status, and ordered by date where engine=1 is a engine start and engine=2 is a engine off event, so every ...
- 101
4
votes
1
answer
3k
views
PostgreSQL - Datetime ranges overlap
I have a table with datetime fields start and end. And I have a list of (start, end) items. I need to check which items from the list overlap with data in the table.
The current query looks like this:
...
- 41
2
votes
1
answer
9k
views
Selecting only overlapping elements from array of ranges
The table I need to search contains an array of numrange values illustrated by:
CREATE TABLE data ( sensor varchar(25), ranges numrange[] );
INSERT INTO data VALUES
('sensor0','{"[872985609.0,...
- 123
3
votes
1
answer
2k
views
Placing a uniqueness constraint on a date range
I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).
How do I place a constraint on the table such that bookings are not ...
- 133
0
votes
1
answer
720
views
Best database system to store and compare date ranges
I'm working on a project which is dealing with a lot of date ranges.
I'm starting completely from scratch; there is no existing code, infrastructure or database. Therefore, I'm searching for the best ...
- 109
1
vote
1
answer
2k
views
Constraint to prevent overlapping ranges in an array field in PostgreSQL
If I have a tztrange field in a table, I can make sure that no two rows have values that overlap using an exclusion constraint. To make sure that no two appointments can be scheduled at the same time, ...
3
votes
1
answer
2k
views
Postgres \copy from CSV: how to format range variables?
Consider a Postgres table containing a range variable:
CREATE TABLE my_table (t_range tsrange, field1 text);
If I want to manually insert a row via psql, I can run:
INSERT INTO my_table (t_range, ...
- 481
1
vote
1
answer
1k
views
Strange upper() behaviour for dateranges in PostgreSQL
I'm running PostgreSQL 9.4.5 and I've just got an unexpected result from this query:
select upper(('[2005-12-01,2005-12-04]')::daterange)
It returns 2005-12-05 instead of 2005-12-04.
The following ...
- 716
3
votes
1
answer
1k
views
Measure time duration between events, partitioned by hours
I have the following table in PostgreSQL 9.4, which persists users log on/off events (log on is event_type 1, log off is event_type 0).
CREATE TABLE user_online_offline_events (
id serial,
...
- 205
0
votes
1
answer
313
views
Insert into int8range from columns of another table
I have two tables in PostgreSQL:
Table indexces_grid:
+---------------+------------+
| Column Name | Type |
+---------------+------------+
| indexid | bigint |
| iprange | ...
- 1
30
votes
5
answers
15k
views
Combining separate ranges into largest possible contiguous ranges
I'm trying to combine multiple date ranges (my load is about max 500, most cases 10) that may or may not overlap into the largest possible contiguous date ranges. For example:
Data:
CREATE TABLE ...
- 587
5
votes
3
answers
1k
views
PostgreSQL: result of range difference would not be contiguous
How to substract a smaller subrange from a bigger range?
123456789
567
Result:
1234 89
SQL:
select '[1,9]'::int4range - '[5,7]'::int4range;
ERROR: result of range difference would not be ...
- 1,421
3
votes
1
answer
3k
views
Updating table of versioned rows with historical records in PostgreSQL
I have a master table of versioned rows:
CREATE TABLE master (
id SERIAL PRIMARY KEY,
rec_id integer,
val text,
valid_on date[],
valid_during daterange
);
INSERT INTO master (...
- 133
4
votes
1
answer
4k
views
Maximum sum() of overlapping ranges
Essentially my question is: how does one do aggregate operations involving overlapping ranges in PostgreSQL 9.3 (or 9.4)? The specific problem I have at hand is that given a range, I want to find the ...
- 173
1
vote
1
answer
187
views
how to update the value of a period contained inside a bigger range
I have a tsrange like
'[2010-01-01 14:00, 2010-01-01 15:00]',20
And want to update the 20 value to 15 in a range inside that, like
'[2010-01-01 14:10, 2010-01-01 14:30]',15
My expected outcome ...
- 339
29
votes
2
answers
1k
views
How to handle bad query plan caused by exact equality on range type?
I'm performing an update where I require an exact equality on a tstzrange variable. ~1M rows are modified, and the query takes ~13 minutes. The result of EXPLAIN ANALYZE can be seen here, and the ...
- 481