Using DISTINCT ON for a column of type timestamp to extract the most recent value for each day

Question

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?

Answer 1

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.

             SELECT MAX(business_time) business_time,
                    business_time::date day
               FROM assets
              WHERE name = 'USD_RLS'
              GROUP BY business_time::date

This subquery returns the latest timestamp for each calendar day.

Then use that list of timestamps to get the detail records you need.

SELECT value, business_time::date day
  FROM assets
 WHERE business_time IN (
                 SELECT MAX(business_time) business_time
                   FROM assets
                  WHERE name = 'USD_RLS'
                  GROUP BY business_time::date )
   AND name = 'USD_RLS'
 ORDER BY business_time::date;

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.

Answer 2

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.

select distinct on (business_time::date) 
       business_time, value
  from assets
 where name = 'USD_RLS'
 order by business_time::date, business_time desc;

NOTE: Not tested, no data supplied.