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?

O. Jones · Accepted Answer · 2022-12-16T19:48:39.750

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.

Thanks. this works. There is just a slight issue. the latest entry is always at business_time 23:50:00 but this query is always taking the value for business_time 22:50 instead of 23:50. I'm almost sure this is due to timezone. I'm in +1:00 UTC. — MehdiB, Dec 16 '22 at 21:15

score 1 · Answer 2 · answered Dec 16 '22 at 20:42

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.

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

2 Answers2