agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedSelect values by interval
5+ messages / 3 participants
[nested] [flat]
* Select values by interval
@ 2015-11-23 16:37 Markus Wolters <MarkusWolters@gmx.de>
2015-11-23 16:49 ` Re: Select values by interval David G. Johnston <david.g.johnston@gmail.com>
2015-11-23 17:22 ` Re: Select values by interval Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 2 replies; 5+ messages in thread
From: Markus Wolters @ 2015-11-23 16:37 UTC (permalink / raw)
To: pgsql-sql
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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Select values by interval
2015-11-23 16:37 Select values by interval Markus Wolters <MarkusWolters@gmx.de>
@ 2015-11-23 16:49 ` David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2015-11-23 16:49 UTC (permalink / raw)
To: Markus Wolters <MarkusWolters@gmx.de>; +Cc: pgsql-sql
On Mon, Nov 23, 2015 at 9:37 AM, 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?
>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Select values by interval
2015-11-23 16:37 Select values by interval Markus Wolters <MarkusWolters@gmx.de>
@ 2015-11-23 17:22 ` Andreas Kretschmer <akretschmer@spamfence.net>
2015-11-23 17:25 ` Re: Select values by interval David G. Johnston <david.g.johnston@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Andreas Kretschmer @ 2015-11-23 17:22 UTC (permalink / raw)
To: pgsql-sql
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*
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°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Select values by interval
2015-11-23 16:37 Select values by interval Markus Wolters <MarkusWolters@gmx.de>
2015-11-23 17:22 ` Re: Select values by interval Andreas Kretschmer <akretschmer@spamfence.net>
@ 2015-11-23 17:25 ` David G. Johnston <david.g.johnston@gmail.com>
2015-11-23 17:31 ` Re: Select values by interval Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2015-11-23 17:25 UTC (permalink / raw)
To: Andreas Kretschmer <akretschmer@spamfence.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Select values by interval
2015-11-23 16:37 Select values by interval Markus Wolters <MarkusWolters@gmx.de>
2015-11-23 17:22 ` Re: Select values by interval Andreas Kretschmer <akretschmer@spamfence.net>
2015-11-23 17:25 ` Re: Select values by interval David G. Johnston <david.g.johnston@gmail.com>
@ 2015-11-23 17:31 ` Andreas Kretschmer <akretschmer@spamfence.net>
0 siblings, 0 replies; 5+ messages in thread
From: Andreas Kretschmer @ 2015-11-23 17:31 UTC (permalink / raw)
To: pgsql-sql
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°
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2015-11-23 17:31 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-11-23 16:37 Select values by interval Markus Wolters <MarkusWolters@gmx.de>
2015-11-23 16:49 ` David G. Johnston <david.g.johnston@gmail.com>
2015-11-23 17:22 ` Andreas Kretschmer <akretschmer@spamfence.net>
2015-11-23 17:25 ` David G. Johnston <david.g.johnston@gmail.com>
2015-11-23 17:31 ` Andreas Kretschmer <akretschmer@spamfence.net>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox