2022 Developer Survey is open! Take survey.

Questions tagged [interval]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
-2 votes
0 answers
17 views

Justify interval in postgres

Input is in interval: 00:19:36 01:53:48 14:15:35 Expected results: 19 Minutes 36 Seconds 01 Hour 53 Minutes 48 Seconds 14 Hours 15 Minutes 35 Seconds
user avatar
0 votes
1 answer
39 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:...
user avatar
  • 5
5 votes
2 answers
188 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 ...
user avatar
0 votes
0 answers
34 views

Redshift extract function not being consistent

I'm trying to move Postgres(v 11) queries to redshift(v 8). I encountered a problem with date_part. When I try to run this, I'm getting the below error. select date_part('hour', modified_on - ...
user avatar
1 vote
1 answer
59 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 ...
user avatar
-1 votes
1 answer
493 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 ...
user avatar
4 votes
2 answers
866 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 ...
user avatar
  • 477
0 votes
0 answers
29 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 ...
user avatar
1 vote
1 answer
91 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 ...
user avatar
  • 13
0 votes
1 answer
2k 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 ...
user avatar
  • 145
2 votes
2 answers
310 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 ...
user avatar
  • 23
0 votes
3 answers
122 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 ...
user avatar
0 votes
1 answer
60 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 ...
user avatar
0 votes
1 answer
3k 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 ...
user avatar
0 votes
1 answer
218 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 ...
user avatar
0 votes
1 answer
32 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. ...
user avatar
  • 123
0 votes
1 answer
52 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. ...
user avatar
1 vote
1 answer
139 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 ...
user avatar
  • 25
0 votes
1 answer
96 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 ...
user avatar
  • 528
0 votes
1 answer
36 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 ...
user avatar
  • 103
0 votes
2 answers
289 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 ...
user avatar
6 votes
3 answers
3k 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 ...
user avatar
-1 votes
1 answer
493 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 ...
user avatar
  • 55.8k
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....
user avatar
  • 125
8 votes
2 answers
7k 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 ...
user avatar
  • 669
5 votes
3 answers
4k 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 ...
user avatar
  • 866
7 votes
1 answer
10k 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(...
user avatar
8 votes
1 answer
791 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 ...
user avatar
1 vote
1 answer
150 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 @...
user avatar
  • 3,546
6 votes
3 answers
12k 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 ...
user avatar
  • 63
0 votes
1 answer
230 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 --------+---------+--------...
user avatar
1 vote
1 answer
1k 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 ...
user avatar
  • 149
2 votes
2 answers
110 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 ...
user avatar