Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [interval]

31 questions
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…
Yogesch
  • 669
  • 1
  • 8
  • 15
8
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
  • 425
  • 1
  • 6
  • 14
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
  • 689
  • 8
  • 20
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
  • 249
  • 5
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
  • 836
  • 7
  • 22
3
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
  • 415
  • 3
  • 13
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
  • 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,317
  • 1
  • 11
  • 25
1
vote
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
  • 15
  • 3
1
vote
1 answer

Function to compute difference between 2 times

I want to create a simple function in Postgres to find the difference between 2 TIME - not TIMESTAMP. As shown below, it accepts 4 parameters: hour, minute, second and expire (hour). In this example I have commented out seconds, just working on…
Tomus85
  • 13
  • 2
1
vote
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.
  • 13
  • 3
1
vote
1 answer

Align values to 5 minute intervals

I have values inserted with random intervals in the table. I would like to fetch the first (if exists) value in (say) 5 minutes interval. So is required to have 5 minutes column and the values column should express the first possible value before…
edo
  • 25
  • 5
1
vote
1 answer

Is it possible to switch queries based on an input in mySQL?

Is there a way to switch queries based on values? Let's say I have the below query: select pdate as 'Payment Date', ROUND(@sum_amount := @sum_amount + `amount`) AS 'Total Amount' from ( select @sum_amount := 0, date(issue_date) pdate, sum(amount)…
Alireza
  • 3,526
  • 10
  • 34
  • 41
1
2 3