Questions tagged [interval]
The interval tag has no usage guidance.
28
questions
-1
votes
1answer
65 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
...
3
votes
2answers
313 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 ...
0
votes
0answers
18 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
1answer
54 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 ...
0
votes
1answer
361 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 ...
1
vote
2answers
107 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 ...
0
votes
2answers
62 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
1answer
58 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
1answer
1k 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
1answer
146 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
1answer
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. ...
0
votes
1answer
51 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
1answer
84 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 ...
0
votes
1answer
64 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 ...
0
votes
1answer
35 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 ...
0
votes
2answers
224 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 ...
5
votes
3answers
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 ...
-1
votes
1answer
435 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 ...
2
votes
2answers
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....
7
votes
2answers
6k 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 ...
5
votes
3answers
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 ...
7
votes
1answer
8k 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(...
7
votes
1answer
695 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
1answer
124 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 @...
6
votes
3answers
11k 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 ...
0
votes
1answer
188 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
1answer
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 ...
2
votes
2answers
101 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
...
