postgres query between yesterday at time and today at time

Question

I am fairly new to postgres and I'm struggling with a query I have to write to get all records between yesterday at 18:00 and today at 18:00.

So far I've tried a predicate like this but it doesn't seem to be right because whilst the query runs I don't get the desired result...

WHERE 
   recon_vr.paymentDateTime >= CURRENT_DATE -1 + '18:00:00.000000'::time
AND    
   recon_vr.paymentDateTime >= CURRENT_DATE + '18:00:00.000000'::time

Could someone please help me with this?

Also, I am running the query from an integration tool which I have found sometimes has issues with the :: syntax. So any solutions without that would be greatly appreciated. But if none exists then that's fine too.

thanks in advance

Answer 1

Your query is right in general, but has a mistake, wrong operator – you used 2 >= instead of >= and <=.

So the correct one is:

WHERE 
   recon_vr.paymentDateTime >= CURRENT_DATE - 1 + '18:00:00.000000'::time
AND    
   recon_vr.paymentDateTime <= CURRENT_DATE + '18:00:00.000000'::time

Also, you could use BETWEEN:

WHERE 
   recon_vr.paymentDateTime BETWEEN CURRENT_DATE - 1 + '18:00:00.000000'::time AND CURRENT_DATE + '18:00:00.000000'::time

To avoid issues with ::time you can write it like this:

WHERE 
   recon_vr.paymentDateTime BETWEEN CURRENT_DATE - 1 + TIME '18:00' AND CURRENT_DATE + TIME '18:00'

...or like this:

WHERE 
   recon_vr.paymentDateTime BETWEEN CURRENT_DATE - 1 + INTERVAL '18h' AND CURRENT_DATE + INTERVAL '18h'

BTW, using CamelStyle object names in Postgres can lead to some unpleasant issues – by default, all names are case-insensitive (so you can easily write recon_vr.paymentDateTime in your case), unless you've used double-quoting at creation time. If so, you'll need to use double quoting forever (recon_vr."paymentDateTime" gives you pure camel-styled colunm name, but you'll need to use double quotes to work with it, not that convenient).