Questions tagged [range-types]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
3
votes
1answer
39 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' ...
2
votes
1answer
120 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)"} ...
1
vote
1answer
34 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 ...
8
votes
2answers
708 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,...
2
votes
1answer
60 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 ...
1
vote
1answer
41 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 ...
1
vote
1answer
280 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 ...
0
votes
0answers
32 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 ...
1
vote
1answer
454 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 ...
1
vote
1answer
61 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 ...
4
votes
1answer
6k 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
628 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
287 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 ...
2
votes
1answer
972 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
1k 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
1k 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
124 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
747 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::...
5
votes
1answer
815 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 ...
6
votes
2answers
5k 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
275 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
838 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
3k 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 ...
4
votes
1answer
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: ...
2
votes
1answer
8k 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
1k 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
465 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
1k 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
1answer
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, ...
1
vote
1answer
713 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 ...
2
votes
1answer
778 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
178 views

Insert into int8range from columns of another table

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