Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [interval]

38 questions
9
votes
1 answer

How to check if there are no time gaps in PostgreSQL?

Schema: CREATE TABLE "expenses_commissionrule" ( "id" serial NOT NULL PRIMARY KEY, "country" varchar(2) NOT NULL, "created" timestamp with time zone NOT NULL, "modified" timestamp with time zone NOT NULL, "activity" tsrange…
Stranger6667
  • 475
  • 1
  • 6
  • 15
8
votes
2 answers

Passing value of datatype interval in parametrized query

The context is connecting to a Postgres db from a rest server. To consider a hypothetical representative example: I would like to be able to get a list of names where the account creation date is older/newer than an arbitrary value. In the example…
ahron
  • 833
  • 2
  • 10
  • 20
7
votes
1 answer

Generic end date of quarter, PostgreSQL

I'd like to produce the generic quarter end date for a given date. Ex:If I have 2010-01-01, I would like to return 2010-03-31, and so on. I can get the quarter number and year: select to_char(date_trunc('quarter', current_date)::date,…
mountainclimber11
  • 759
  • 1
  • 10
  • 23
6
votes
2 answers

How to add a generated column with an expression subtracting days?

I have this table in PostgreSQL 13: CREATE TABLE public."domain" ( id int8 NOT NULL GENERATED ALWAYS AS IDENTITY, domain_name varchar NOT NULL, -- more columns expire_date timestamp NULL, days_before_trigger int4 NOT NULL DEFAULT…
Dolphin
  • 929
  • 5
  • 21
  • 40
6
votes
3 answers

Select all overlapping ranges from starting range

My question is similar to this one with (I think) significant enough difference. I have a base range and I want to find all other ranges in a table that conflict with it and each other. or rather the items that form the ranges, but it doesn't really…
rancor1223
  • 163
  • 1
  • 5
6
votes
3 answers

MySQL - Merge or split datetime intervals (start date to end date)

I have a table which stores a list of activities with an interval of time delimited by 2 dates. Sample: +------+---------------------+---------------------+-------------+ | name | start | end | time (calc)…
vgc
  • 63
  • 1
  • 1
  • 5
5
votes
2 answers

Computing the set difference of tables of intervals

I often run into the following problem. I have two tables of intervals. They are bounded by dates (with no time component). Within each table the intervals do not…
Rushabh Mehta
  • 251
  • 2
  • 7
5
votes
3 answers

How do I round time to the upper multiple of an arbitrary time interval?

Example: If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '5 minute', then the desired output is 2018-05-17 22:50:00. If the current time is 2018-05-17 22:45:30 and the desired interval is INTERVAL '10 minute', then…
Gajus
  • 1,334
  • 16
  • 29
3
votes
2 answers

Convert overlapping datetime ranges into non-overlapping ranges with priorities

I have data stored in a table like this: CREATE TABLE Records ( [Id] int IDENTITY(1,1) not null ,[From] datetime not null ,[To] datetime not null ,[Priority] int not null ) Every row contains time record with from-to range and its…
Tom C.
  • 33
  • 4
2
votes
1 answer

Convert Varchar to Interval

I have a Postgresql column that is a varchar that is saving a string that looks like '2 years, 5 months' or '3 months' or '9 years, 0 months'. I can't convert the column to an interval because it's going to break a bunch of existing functionality…
thesofaking
  • 27
  • 4
2
votes
2 answers

Select results inclusively based on NOW()

My query is the following: SELECT count(db.user_data.page_url) AS total_urls, db.authors.author FROM db.authors LEFT JOIN db.user_data ON db.authors.author_id = db.user_data.id WHERE (db.user_data.date > (now() - INTERVAL 30 MINUTE)) AND…
Sid
  • 125
  • 1
  • 6
2
votes
2 answers

Get the date interval from table rows, based on a start stop column

I have a table returning the following result set: mydate | startstop ------------+---------- 2018-02-07 | start 2018-02-14 | stop 2017-02-06 | start 2017-02-12 | stop 2016-02-05 | start 2016-02-12 | stop I need to know if my current…
Luciano Andress Martini
  • 1,611
  • 2
  • 15
  • 30
1
vote
0 answers

Tool/library/function creating time-interval/state-duration report from a time/state series

I am looking for a standard method, a library or maybe best practice approach that can generate a time-interval/state-duration report from a time/state series. This seems like a common reporting requirement for time/state data, and I would assume…
DaveX
  • 11
  • 1
1
vote
1 answer

Split intervals with two tables

I have two tables that allocate employees to cost centers. The first table (CCmain) shows the primary cost center. If an employee is assigned to more than one cost center, the second table (CCfraction) shows what fraction of the employee is assigned…
Stefan
  • 13
  • 3
1
vote
2 answers

Prohibit overlapping intervals in a cyclic (e.g. weekly) schedule

I'm designing a postgres table to record the intervals contained in a weekly schedule. It would hold schedules for multiple businesses, and a simple example dataset might look like: business_id interval ----------- …
ivan
  • 552
  • 2
  • 11
1
2 3