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

Answer 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?'.

Answer 2

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

Answer 3

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'