Questions tagged [range-types]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1
vote
1answer
33 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 ...
1
vote
1answer
234 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 ...
3
votes
3answers
314 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 ...
1
vote
2answers
116 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 ...
1
vote
1answer
170 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....
1
vote
2answers
456 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 -------------...
3
votes
2answers
624 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 ...
2
votes
1answer
98 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:...
3
votes
2answers
448 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::...
3
votes
1answer
495 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 ...
4
votes
2answers
2k 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 ...
3
votes
6answers
270 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 | ...
4
votes
1answer
603 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, ...
5
votes
3answers
2k 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['...
0
votes
1answer
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 ...
3
votes
1answer
1k 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: ...
2
votes
1answer
4k 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,...
2
votes
1answer
584 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 ...
0
votes
1answer
311 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 ...
0
votes
1answer
711 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, ...
2
votes
1answer
1k 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, ...
1
vote
1answer
441 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 ...
1
vote
2answers
67 views

Define a db before coding

I'm starting to code time tracking application to replace the monthly excel sheets my company is requiring. Its purpose is standard : to centralize all data about the time spent by different(or same) ...
2
votes
1answer
382 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, ...
0
votes
1answer
118 views

Insert into int8range from columns of another table

I have two tables in PostgreSQL: Table indexces_grid: +---------------+------------+ | Column Name | Type | +---------------+------------+ | indexid | bigint | | iprange | ...
20
votes
4answers
7k 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 ...
4
votes
3answers
601 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 ...
2
votes
1answer
1k 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 (...
3
votes
2answers
3k 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 ...
1
vote
1answer
167 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 ...
28
votes
2answers
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 ...