Get Latest Data from Today and Yesterday on PostgreSQL?-CodePudding

I have some time-series data I'd like to get certain information about.

How would I retrieve two records, where I get today and yesterday's latest record?

Here's some sample data of the data I'm working with.

latest  price       amount  avg_rating  time
4.9     13323.92256 153     5.466540778 2022-03-05 23:55:56.852459-05 <--- Today's latest record
4.99    13318.92256 154     5.490350302 2022-03-05 23:17:55.814162-05   
5.7     13009.62049 151     6.5314      2022-03-05 02:39:39.826069-05   
5.99    12997.92049 154     6.571948718 2022-03-05 01:13:48.528526-05   
5.99    12911.34049 150     6.867946429 2022-03-04 18:14:01.177497-05 <--- Yesterday's latest record
5.99    12911.34049 151     6.845169492 2022-03-04 17:34:05.538811-05   
5.99    12911.34049 153     6.845169492 2022-03-04 17:08:19.254631-05

What I want is below

latest  price       amount  avg_rating  time
4.9     13323.92256 153     5.466540778 2022-03-05 23:55:56.852459-05
5.99    12911.34049 150     6.867946429 2022-03-04 18:14:01.177497-05

CodePudding user response：

Extract only date from time column and use it in PARTITION BY clause. As today and yesterday data is needed then use current_date for today and current_date - 1 for yesterday for retrieving two date data. This query is applicable for all date for this disable WHERE clause. Date wise most recent data is picked as per requirement by using subquery.

SELECT t.latest
     , t.price
     , t.amount
     , t.avg_rating
     , t.time
FROM (SELECT *
           , ROW_NUMBER() OVER (PARTITION BY time :: VARCHAR(10) :: DATE ORDER BY time DESC) row_num
      FROM table_name
      WHERE time :: VARCHAR(10) :: DATE BETWEEN current_date - 1 AND current_date) t
WHERE t.row_num = 1