Questions tagged [interval]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
2 answers
136 views

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 ...
  • 552
1 vote
1 answer
895 views

Count total data with time interval in Postgresql

I have an issue that I want to count total data with 1 hour interval. However start time is 7:30 Time Status 2022-03-24 07:36:00, Fail 2022-03-24 07:59:00, Pass 2022-03-24 09:32:...
  • 15
5 votes
2 answers
304 views

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 overlap. StartTs EndTs 2015-01-03 ...
1 vote
1 answer
317 views

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 ...
-1 votes
1 answer
988 views

PostgreSQL Query - how to subtract timestamps and format durations (with builitin functions or otherweise)

I am looking for a solution to a problem with a select statement where I am trying to display the sleep duration. Sleep Table Here is the select statement I have so far: select wakeuptime,hour from ...
4 votes
2 answers
2k views

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 ...
  • 621
0 votes
0 answers
122 views

Postgres cast MI:SS formatted string to "interval minute to second" type

I have a data source with the time information being formatted like this: 'MI:SS'. I am trying to coerce that to a native postgres INTERVAL or TIME type so I can leverage the equivalent operators. I ...
1 vote
1 answer
168 views

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 ...
  • 13
0 votes
1 answer
7k views

Get the start and end dates of the previous month the query is run with PostgreSQL/intervals

I am looking for a way to get the start and end datetime for the previous month the query is run that I can reuse inside a query. I was looking at interval but I would like the query to work out that ...
  • 145
2 votes
2 answers
981 views

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 ...
  • 23
0 votes
3 answers
649 views

Split intervals

I have two tables. Each holds some attributes for a business entity and the date range for which those attributes were valid. I want to combine these tables into one, matching rows on the common ...
0 votes
1 answer
61 views

Flag Rows Occurring 2 Hours After Previous Flag

I have some messy RFID data due to over sensitive antenna's. There is a physical process that tracks an RFID tag moving through different stations in a cycle. An item with an RFID tag can move through ...
0 votes
1 answer
4k views

operator does not exist: interval < date

I want to do the following comparison : IF ( V_C_CRITERE2.DATE_FIN_SOUHAITEE - TO_DATE(V_SEUIL_ALERT) < CURRENT_DATE) THEN DATE_FIN_SOUHAITEE is a DATE. But a get this error : operator does ...
0 votes
1 answer
519 views

Calculate the duration in an interval and outside of events that can last over several days

I need to calculate the total duration of events in a given time interval and on a given day of the week with PostgreSQL 11 For an event on the same day no problem. But over several days I manage to ...
0 votes
1 answer
38 views

Dates, Times and Timezones - How are they incorporated in Interval?

Does interval '1 month' incorporate month differences and leap years when calculating date/datetime boundaries? If so, how does it work? I was asked this question, and I'm not sure how to answer it. ...
  • 123
0 votes
1 answer
53 views

How do I form an SQL query to show entire column of dates with modified values?

Assume that I have a table Purchases with column named PurchaseDate and with data format of 'YYYY-MM-DD HH:MI:SS'. Suppose that I want to see the entire column of dates with the days increasing by 5. ...
1 vote
1 answer
830 views

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 ...
  • 25
0 votes
1 answer
273 views

How to group time intervals by order of magnitude in Postgres?

I have a bunch of time intervals that I would like to analyze. (Thousands of them.) They range from 0 seconds up to several thousand days, but most of them are less than ten minutes. I would like ...
  • 578
0 votes
1 answer
45 views

Return hours, minutes and seconds left if column wasn't updated

I currently have this query: UPDATE MyTable SET next_update = now() at time zone 'utc' WHERE next_update::timestamp < now() at time zone 'utc' - interval '1 day'; Which updates the column ...
  • 103
0 votes
2 answers
390 views

Get value increment during an hour of a day

I query YouTube Data Api for a list of most popular videos on a channel and then get their statistics, 4 times per hour (each 15 minutes, by cron). The data is stored in Postgres, but dumping it and ...
6 votes
3 answers
4k views

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 ...
-1 votes
1 answer
576 views

'INTERVAL' is not a recognized ODBC date/time extension option

According to this docs on Interval Literals, this should be a valid INTERVAL, SELECT {INTERVAL '326' YEAR(4)}; But when I try that on dbfiddle, I get, Msg 155 Level 15 State 1 Line 1 'INTERVAL' is ...
  • 59.6k
2 votes
2 answers
2k views

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....
  • 125
8 votes
2 answers
8k views

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 ...
  • 749
5 votes
3 answers
6k views

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 ...
  • 1,076
7 votes
1 answer
12k views

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(...
9 votes
1 answer
894 views

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 ...
1 vote
1 answer
193 views

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 @...
  • 3,586
6 votes
3 answers
13k views

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 ...
  • 63
0 votes
1 answer
300 views

Repeating rows of date intervals Between dates Firebird

I am stuck in a case that on my financial sys, I need to add a option for value to be monthly, weekly or every 2 weeks example: DESC | VALUE | DATEADD | FREQUENCY --------+---------+--------...
1 vote
1 answer
2k views

Informix Older than 15 Minutes

How can I do this MySQL statement in an IBM Informix? select type, channel, teilnr, starttime, endtime, usedtime, host from online_time where starttime < DATE_SUB(NOW(),INTERVAL 15 MINUTE) order ...
  • 149
2 votes
2 answers
130 views

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 ...