Get most recent data to a periodic timestamp

Question

edit

I've realised that my question really has two parts:

How do I group by time periods, and
How do I select a particular row in a group?.

One of the answers to the second question uses Postgres' SELECT DISTINCT ON, which means I don't need a group at all. I've posted my solution below.

I have data which is normally queried to get the most recent value. However I need to be able to reproduce what results would have been received if I'd queried every minute, going back to some timestamp.

I don't really know where to start. I have very little experience with SQL.

CREATE TABLE history
(
  detected timestamp with time zone NOT NULL,
  stat integer NOT NULL
)

I select like:

SELECT
detected,
stat

FROM history

WHERE
detected > '2013-11-26 20:19:58+00'::timestamp

Obviously this gives me every result since the given timestamp. I want every stat closest to minutes going back from now to the timestamp. By closest I mean 'less than'.

Sorry I haven't made a very good effort of getting anywhere near the answer. I'm so unfamiliar with SQL I don't know where to begin.

edit

This question, How to group time by hour or by 10 minutes, seems helpful:

SELECT timeslot, MAX(detected)
FROM
(  
    SELECT to_char(detected, 'YYYY-MM-DD hh24:MI') timeslot, detected
    FROM
    (
        SELECT detected
        FROM history
        where
        detected > '2013-11-28 13:09:58+00'::timestamp
    ) as foo 
) as foo GROUP BY timeslot

This gives me the most recent detected timestamp on one minute intervals.

How do I get the stat? The MAX is run on all the detected grouped by minutes, but the stat is inaccessible.

2nd edit

I have:

timeslot;max
"2013-11-28 14:04";"2013-11-28 14:04:05+00"
"2013-11-28 14:17";"2013-11-28 14:17:22+00"
"2013-11-28 14:16";"2013-11-28 14:16:40+00"
"2013-11-28 14:13";"2013-11-28 14:13:31+00"
"2013-11-28 14:10";"2013-11-28 14:10:02+00"
"2013-11-28 14:09";"2013-11-28 14:09:51+00"

I would like:

detected;stat
"2013-11-28 14:04:05+00";123
"2013-11-28 14:17:22+00";125
"2013-11-28 14:16:40+00";121
"2013-11-28 14:13:31+00";118
"2013-11-28 14:10:02+00";119
"2013-11-28 14:09:51+00";121

max and detected are the same

@SamS That would return one result, but I need as many results as minutes have passed since the timestamp. — Peter Wood, Nov 27 '13 at 15:24
I see what you need. One way to achieve this is to copy the data to another database and modify your query to give you the results greater than the max timestamp on the second table. I'll try to make a sqlfiddle — Sam, Nov 27 '13 at 15:29
You can also make a Boolean column moved default to 0 and when you move it to the second table change it to 1 — Sam, Nov 27 '13 at 16:27
I'm not sure about copying to another database. The table currently has about 2 million rows. — Peter Wood, Nov 28 '13 at 10:54
So what you basically want is a query which gives you all stats from minuten 36 to 37 and then another one for all stats from 37 to 38? Or do you want all in one query and if so, how do you want your result to look like (in terms of formatting)? — Armunin, Nov 28 '13 at 13:56
I want one `stat` for each minute period, the most recent `stat` in that period. — Peter Wood, Nov 28 '13 at 14:17

DasMensch · Answer 1 · 2013-11-27T17:20:46.130

Okay another try :)

I tried it with my AdventureWorks DB from Microsoft. I took some other datatypes but it should work with datetimeoffset or similar datetimes too.

So i tried it with a loop. While your timestamp is less than NOW, select for me the data between your timestamp and the timestamp plus the interval size. With that i get the data in one interval, and then i set the timestamp plus the interval to get the next, till the while loop arrives at today. Maybe that is a way, if not sorry for that :)

DECLARE @today date
DECLARE @yourTimestamp date
DECLARE @intervalVariable date

SET @intervalVariable = '2005-01-07' -- start at your timestamp
SET @today = '2010-12-31'

WHILE  @intervalVariable < @today -- your Timestamp on the left side
BEGIN

SELECT FullDateAlternateKey FROM dbo.DimDate
WHERE FullDateAlternateKey BETWEEN @intervalVariable AND DATEADD(dd,3,    @intervalVariable)

SET @intervalVariable = DATEADD(dd,3, @intervalVariable) -- the three is your intervale
print 'interval'
END
print 'Nothing or finished'

Sorry, I haven't explained myself well. Imagine I've missed polling my database for 5 minutes. I want to be able to say "give me the last 5 minutes of `stat` as they would have been if I'd polled on 1 minute intervals". — Peter Wood, Nov 27 '13 at 16:23
Er, please don't use `BETWEEN` [with date/time/timestamp types](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common), _especially_ on SQL Server. — Clockwork-Muse, Dec 03 '13 at 11:28

score 1 · Accepted Answer · edited Dec 03 '13 at 08:36

I can offer you this solution:

with t (tstamp, stat) as(
  values 
    (  current_timestamp,                         'stat1'), 
    (  current_timestamp - interval '50' second,  'stat2'),
    (  current_timestamp - interval '100' second, 'stat3'),
    (  current_timestamp - interval '150' second, 'stat4'),
    (  current_timestamp - interval '200' second, 'stat5'),
    (  current_timestamp - interval '250' second, 'stat6')
)
select stat, tstamp
from t
where tstamp in (
    select max(tstamp)
    from t
    group by date_trunc('minute', tstamp)
);

But it is in Oracle... maybe it helps you anyway

I took the liberty to convert this to Postgres syntax – Dec 03 '13 at 08:36 — , Dec 03 '13 at 08:36

score 1 · Answer 3 · edited May 23 '17 at 11:58

1

My solution combines clipping timestamps to the nearest minute using to_char, and selecting the first rows with distinct minutes:

SELECT DISTINCT ON (timeslot)
to_char(detected, 'YYYY-MM-DD hh24:MI') timeslot,
detected,
stat

FROM history

ORDER BY timeslot DESC, detected DESC;

This was arrived at by this answer to 'Select first row in each GROUP BY group?'.

edited May 23 '17 at 11:58

Community

1
1

answered Dec 03 '13 at 08:22

Peter Wood

23,859
5
60
99

Are you sure, you get the newest entry per minute this way and not just 1 random entry per minute as you do the sorting after you clip your date – Armunin Dec 03 '13 at 08:42
@Armunin [`SELECT DISTINCT ON`](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT) uses the `ORDER BY` to select one row per timeslot. – Peter Wood Dec 03 '13 at 10:37
@Armunin I fixed the syntax too. I had missing parens round the `timeslot`. – Peter Wood Dec 03 '13 at 10:38
thanks for the update, I am not that familiar with postgresql. Happy you found a solution. – Armunin Dec 03 '13 at 10:41

Get most recent data to a periodic timestamp

3 Answers3