Postgres period query

Question

I have a table which looks like the following:

 index  | timestamp         | type | value 
------- | ----------------- | ---- | ----- 
  1     | 2014-07-10 10:00  | A    | 56
  2     | 2014-07-10 11:04  | B    | 69
  3     | 2014-07-10 11:06  | C    | 68
  4     | 2014-07-10 10:03  | B    | 60
  5     | 2014-07-11 10:03  | B    | 18
  6     | 2014-07-11 11:03  | C    | 48

I am willing to select a period of values for certain 'types'.

The problem is that I also need the result to include the last value of each type before the selected period starts.

Any idea if it is possible to achieve with a single query (or at list limited number of queries)?

Thanks Meir

Selecting all types for perid timestamp >= '2014-07-11', I want to have all lines except 4 (because last value of type B before 2014-07-11 is in line 2) — Meir Tseitlin
– Meir Tseitlin, Commented Aug 3, 2017 at 15:13

Meir Tseitlin · Accepted Answer · 2017-08-03 16:04:33Z

1

Eventually I found my own answer

SELECT timestamp, type, value
FROM   table
where timestamp >= '2014-07-11'
UNION ALL
SELECT distinct on (type) timestamp, type, value
FROM   table
where timestamp < '2014-07-11'
ORDER BY type, timestamp desc

answered Aug 3, 2017 at 16:04

Meir Tseitlin

2,0483 gold badges19 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wargre · Accepted Answer · 2017-08-03 15:05:38Z

0

You can use window function especially rank() :

https://www.postgresql.org/docs/current/static/tutorial-window.html

you will have somethink like :

select * from (
    select ...., rank() over (partition by type order by timestamp desc) as rk 
    where ... 
) where rk=1;

I don't remember if you can put 'rk' in a 'having' clause of the inner query, but I'm pretty sure I already try that once, and postgres did not accept that

edited Aug 3, 2017 at 15:05

user330315

answered Aug 3, 2017 at 14:54

wargre

4,7831 gold badge24 silver badges41 bronze badges

Collectives™ on Stack Overflow

Postgres period query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related