CODEWITHSUNDEEP
sqlpostgresqlselecttime-series
Siddiq Nx
Siddiq Nx

Reputation: 122

How to get the latest values day wise from a timeseries table?

I want to get the latest values of each SIZE_TYPE day wise, ordered by TIMESTAMP. So, only 1 value of each SIZE_TYPE must be present for a given day, and that is the latest value for the day.

How do I get the desired output? I'm using PostgreSQL here.

Input

|TIMESTAMP                               |SIZE_TYPE|SIZE|
|----------------------------------------|---------|----|
|1595833641356 [Mon Jul 27 2020 07:07:21]|0        |541 |
|1595833641356 [Mon Jul 27 2020 07:07:21]|1        |743 |
|1595833641356 [Mon Jul 27 2020 07:07:21]|2        |912 |
|1595876841356 [Mon Jul 27 2020 19:07:21]|1        |714 |
|1595876841356 [Mon Jul 27 2020 19:07:21]|2        |987 |
|1595963241356 [Tue Jul 28 2020 19:07:21]|0        |498 |
|1595920041356 [Tue Jul 28 2020 07:07:21]|2        |974 |
|1595920041356 [Tue Jul 28 2020 07:07:21]|0        |512 |

*Note: the TIMESTAMP values are in UNIX time. I have given
the date-time string for reference*

Output

|TIMESTAMP                               |SIZE_TYPE|SIZE|
|----------------------------------------|---------|----|
|1595833641356 [Mon Jul 27 2020 07:07:21]|0        |541 |
|1595876841356 [Mon Jul 27 2020 19:07:21]|1        |714 |
|1595876841356 [Mon Jul 27 2020 19:07:21]|2        |987 |
|1595920041356 [Tue Jul 28 2020 07:07:21]|2        |974 |
|1595963241356 [Tue Jul 28 2020 19:07:21]|0        |498 |

*Note: the TIMESTAMP values are in UNIX time. I have given
the date-time string for reference*

Explanation

For July 27, the latest values for

For July 28, the latest values for

Upvotes: 1

Views: 472

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can use distinct on:

select distinct on (floor(timestamp / (24 * 60 * 60 * 1000)), size_type) t.*
from input
order by floor(timestamp / (24 * 60 * 60 * 1000)), size_type,
          timestamp desc;

The arithmetic is just to extract the day from the timestamp.

Here is a db<>fiddle.

Upvotes: 1

Related Questions