Questions tagged [range-types]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
1 answer
30 views

What's this third argument to PostgreSQL `tstzrange`?

On https://www.timescale.com/blog/best-practices-for-time-series-metadata-tables/ there's a function declaration that looks for time range overlaps with: SELECT NOT isempty( tstzrange(...
Nathan Long's user avatar
2 votes
2 answers
266 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 ...
Sig's user avatar
  • 371
1 vote
1 answer
204 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 ...
g4s8's user avatar
  • 111
1 vote
1 answer
298 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 ...
eliangius's user avatar
  • 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 ...
AndreKR's user avatar
  • 557
1 vote
0 answers
174 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 ...
user2855892's user avatar
4 votes
1 answer
294 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' ...
Dmitro's user avatar
  • 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)"} ...
Utente000's user avatar
1 vote
1 answer
884 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 ...
Aww's user avatar
  • 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,...
ERJAN's user avatar
  • 465
2 votes
1 answer
351 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 ...
jamesmstone's user avatar
1 vote
1 answer
82 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 ...
feketegy's user avatar
  • 121
1 vote
2 answers
904 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 ...
Jimmy's user avatar
  • 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 ...
user1201917's user avatar
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 ...
TmTron's user avatar
  • 233
1 vote
1 answer
154 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 ...
Daniel S's user avatar
7 votes
1 answer
15k 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 ...
Zack's user avatar
  • 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 ...
John Marsing's user avatar
1 vote
2 answers
504 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 ...
dimzon's user avatar
  • 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....
Slim's user avatar
  • 221
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 -------------...
John Bachir's user avatar
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 ...
Alexander's user avatar
  • 143
2 votes
1 answer
199 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:...
John Frazer's user avatar
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::...
Rafael Calino's user avatar
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 ...
Victor's user avatar
  • 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 ...
RMathis's user avatar
  • 472
3 votes
6 answers
293 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 | ...
User1974's user avatar
  • 1,447
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, ...
Matjaž's user avatar
  • 353
5 votes
3 answers
5k 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['...
aodj's user avatar
  • 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 ...
RonEskinder's user avatar
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: ...
Anna's user avatar
  • 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,...
terse's user avatar
  • 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 ...
srip's user avatar
  • 133
0 votes
1 answer
741 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 ...
d688's user avatar
  • 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, ...
Michael Underwood's user avatar
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, ...
abeboparebop's user avatar
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 ...
pietrop's user avatar
  • 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, ...
Asaf David's user avatar
0 votes
1 answer
330 views

Insert into int8range from columns of another table

I have two tables in PostgreSQL: Table indexces_grid: +---------------+------------+ | Column Name | Type | +---------------+------------+ | indexid | bigint | | iprange | ...
VP7's user avatar
  • 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 ...
Villiers Strauss's user avatar
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 ...
guettli's user avatar
  • 1,491
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 (...
danpelota's user avatar
  • 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 ...
Rob's user avatar
  • 173
1 vote
1 answer
188 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 ...
avances123's user avatar
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 ...
abeboparebop's user avatar