I have a table named assets:
create table assets (
id bigint primary key,
name varchar(255) not null,
value double precision not null,
business_time timestamp with time zone,
insert_time timestamp with time zone default now() not null
);
I am trying to write a query to get the most recent value
for each day based on business_time
. This is a sample query that I wrote:
select distinct on (business_time::date) business_time::date, value
from home.assets
where name = 'USD_RLS'
order by business_time::date desc
But the value for each day is not always the most recent one. I guess it’s a bit random which value I get for each day. Any idea how I could fix this query?
2
Answers
Here’s a good way to approach this requirement, but not with DISTINCT ON.
Start by getting the maximum value of the
business_time
timestamp value (including date and time) for each calendar day, thusly.This subquery returns the latest timestamp for each calendar day.
Then use that list of timestamps to get the detail records you need.
It’s all about sets. First you find the set of timestamps that are the latest for each calendar day. Then you find the set of rows that have those timestamps.
What you are facing is truncating the time from the timestamp, thus every row for a date has the same time (00:00:00). You can use a slightly unorthodox, but in this case effective sorting. Sort by the date (ascending or descending – it does not matter) then (the unorthodox part) sort by the full timestamp desc (must be descending). That will give the the latest time for each date.
NOTE: Not tested, no data supplied.