Questions tagged [interval]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
38 views

Keep both non-overlapping intervals and overlapping ones with a larger id MySQL

I have the following table in MySQL: data(interval_id, object_id, start, end), where interval_id, object_id are integers and interval_id is unique per every object_id, while object_id itself is unique....
Alex.Kh's user avatar
  • 101
0 votes
0 answers
17 views

Return nothing or all if overlaps fit somewhere PSQL

I have searched a bit and not come across anything comparable, so I'm asking this question here, hoping I haven't overlooked anything. In my situation, we store deliveries on the database, which ...
Ricardo's user avatar
0 votes
1 answer
58 views

How to join matching Date fields as intervals?

Let say I've a table with a list of id/year/month (two years + 24 months for semplicity here): Code Year Month MAT000000001 2022 1 MAT000000001 2022 2 MAT000000001 2022 ...
markzzz's user avatar
  • 243
0 votes
1 answer
529 views

How to efficiently get absolute value of a time interval in Postgresql?

I have a huge table in Postgresql-11 like following: CREATE TABLE my_huge_table( tick_time timestamp(6) with time zone NOT NULL, brok_time timestamp(6) with time zone, trade_day date NOT ...
Leon's user avatar
  • 337
1 vote
2 answers
297 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 ...
ivan's user avatar
  • 552
1 vote
1 answer
2k 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:...
hem6d's user avatar
  • 15
5 votes
2 answers
357 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 ...
Rushabh Mehta's user avatar
1 vote
1 answer
960 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 ...
thesofaking's user avatar
-1 votes
1 answer
1k 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 ...
Badrinarayanan's user avatar
6 votes
2 answers
4k 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 ...
Dolphin's user avatar
  • 775
0 votes
0 answers
257 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 ...
Antonio L.'s user avatar
1 vote
1 answer
355 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 ...
Tomus85's user avatar
  • 13
1 vote
1 answer
11k 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 ...
crmpicco's user avatar
  • 155
3 votes
2 answers
1k 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 ...
Tom C.'s user avatar
  • 33
0 votes
3 answers
929 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 ...
Michael Green's user avatar
0 votes
1 answer
80 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 ...
Hunter Duckworth's user avatar
0 votes
1 answer
6k 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 ...
unknownUser's user avatar
0 votes
1 answer
821 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 ...
Hadrien's user avatar
0 votes
1 answer
42 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. ...
Jason's user avatar
  • 153
0 votes
1 answer
69 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. ...
SQLstudent11242019's user avatar
1 vote
1 answer
1k 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 ...
edo's user avatar
  • 25
0 votes
1 answer
439 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 ...
Wildcard's user avatar
  • 588
0 votes
1 answer
84 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 ...
Blastcore's user avatar
  • 103
0 votes
2 answers
546 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 ...
Eduard Sukharev's user avatar
6 votes
3 answers
5k 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 ...
rancor1223's user avatar
-1 votes
1 answer
666 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 ...
Evan Carroll's user avatar
  • 62.7k
2 votes
2 answers
3k 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....
Sid's user avatar
  • 125
8 votes
2 answers
10k 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 ...
ahron's user avatar
  • 793
5 votes
3 answers
7k 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 ...
Gajus's user avatar
  • 1,175
7 votes
1 answer
14k 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(...
mountainclimber11's user avatar
9 votes
1 answer
1k 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 ...
Stranger6667's user avatar
1 vote
1 answer
230 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 @...
Alireza's user avatar
  • 3,636
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 ...
vgc's user avatar
  • 63
0 votes
1 answer
341 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 --------+---------+--------...
Hermeson's user avatar
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 ...
LStrike's user avatar
  • 149
2 votes
2 answers
149 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 ...
Luciano Andress Martini's user avatar
2 votes
2 answers
3k views

Why does my query for interval differences return one result to few?

I'm trying to query a MySQL database with hourly timeseries data but I'm stuck with (daily) aggregation. My raw data: mysql> SELECT * FROM emoncms.custom_registers -> WHERE timestamp >=...
JMB240517's user avatar