Questions tagged [interval]
The interval tag has no usage guidance.
33
questions
-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
- 23
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:...
- 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
...
- 249
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 - ...
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 ...
- 17
-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
...
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 ...
- 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 ...
- 21
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 ...
- 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 ...
- 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 ...
- 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 ...
- 22.8k
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 ...
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 ...
- 29
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 ...
- 1
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. ...
- 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.
...
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 ...
- 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 ...
- 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 ...
- 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 ...
- 115
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 ...
- 163
-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 ...
- 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....
- 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 ...
- 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 ...
- 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(...
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 ...
- 445
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 @...
- 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 ...
- 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
--------+---------+--------...
- 1
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 ...
- 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
...
- 1,339