Looking to reduce postgres queries to one query-CodePudding

I want to reduce the number of queries I'm making to my postgres database.

Currently I have this

select * from token_balances where asset_id = '36f813e4-403a-4246-a405-8efc0cbde76a' AND tick < yesterday ORDER BY tick DESC LIMIT 1;
select * from token_balances where asset_id = '36f813e4-403a-4246-a405-8efc0cbde76a' AND tick < last_week ORDER BY tick DESC LIMIT 1;

Is there some way I can make this into one query. Or is there even any need to reduce it?

Thanks

CodePudding user response：

You can simply use the query with the greater date of tick. The first query already includes the results coming from the second one.

Just use the first query and you are good to go.

select * from token_balances where asset_id = '36f813e4-403a-4246-a405-8efc0cbde76a' AND tick < '2022-05-26T14:13:42.914Z' ORDER BY tick DESC LIMIT 1;

CodePudding user response：

Rather than use yesterday (which is a valid reference) and last_week (which is not valid) just convert to date arithmetic. You can use simple date subtraction:

select * 
  from token_balances 
 where asset_id = '36f813e4-403a-4246-a405-8efc0cbde76a' 
   and tick < current_date - :num_days
 order by tick desc
 limit 1;