Thread: Select values by interval
Hi all, I have a table with value and timestamp columns. What I like to do (but am unable to find a solution) is to select the last(value)timestamp combination in every X minute interval where timestamp is between N and M. Is this possible with pgsql? Thanks in advance, Markus
Hi all,
I have a table with value and timestamp columns. What I like to do (but am unable to find a solution) is to select the last(value) timestamp combination in every X minute interval where timestamp is between N and M. Is this possible with pgsql?
Look at:
generate_series(...)
date_part(...)
SELECT DISTINCT ON
You should consider providing a query with some test data.
WITH vals (v, ts) AS (
VALUES (1, now()), (2, now() + '2 minutes'::interval), [etc]
)
David J.
Markus Wolters <MarkusWolters@gmx.de> wrote: > Hi all, > > I have a table with value and timestamp columns. What I like to do (but am unable to find a solution) is to select thelast(value) timestamp combination in every X minute interval where timestamp is between N and M. Is this possible withpgsql? > maybe somethink like select *, row_number() over (partition by to_char(timestamp, 'yyyy-mm-dd hh24:mm') order by ... desc) ... and then pick all with row_number = 1 *untested* Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
On Mon, Nov 23, 2015 at 10:22 AM, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
Markus Wolters <MarkusWolters@gmx.de> wrote:
> Hi all,
>
> I have a table with value and timestamp columns. What I like to do (but am unable to find a solution) is to select the last(value) timestamp combination in every X minute interval where timestamp is between N and M. Is this possible with pgsql?
>
maybe somethink like
select *, row_number() over (partition by to_char(timestamp, 'yyyy-mm-dd
hh24:mm') order by ... desc) ...
and then pick all with row_number = 1
*untested*
Unproven but whenever you have a query of this form (row_number = 1) you should consider/test whether using DISTINCT ON with an appropriate ORDER BY clause gives you the answer faster and/or more clearly.
David J.
David G. Johnston <david.g.johnston@gmail.com> wrote: > > Unproven but whenever you have a query of this form (row_number = 1) you > should consider/test whether using DISTINCT ON with an appropriate ORDER BY > clause gives you the answer faster and/or more clearly. Nice hint. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°