Skip to main content

Questions tagged [interval]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
7 views

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 ...
DaveX's user avatar
  • 11
1 vote
1 answer
119 views

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)...
Stefan's user avatar
  • 13
0 votes
0 answers
66 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
84 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
758 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
  • 357
1 vote
2 answers
329 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
359 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
2 votes
1 answer
2k 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
2k 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
5k 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
  • 785
0 votes
0 answers
283 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
397 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
12k 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
  • 165
3 votes
2 answers
2k 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
1k 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
86 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
908 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
44 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
75 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
485 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
92 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
580 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
696 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
  • 63.9k
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
  • 813
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,195
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
237 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,646
6 votes
3 answers
14k 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
360 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
155 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