SQL – to query record that created after latest specific time

I currently have a database which have the created as a timestamp of each record that it was created.

I try to query all the data that was created after the latest midday time (12:00).

So, let’s said data is

id1 | at 12.30 2nd Oct
id2 | at 21.00 2st Oct
id3 | at 12.10 1st Oct

And current time is 3rd Oct 11.59, I would like answer of query to be

id1 | at 12.30 2nd Oct
id2 | at 21.00 2st Oct

But if the data is

id1 | at 12.30 2nd Oct
id2 | at 21.00 2st Oct
id3 | at 12.10 3rd Oct

And current time is 3rd Oct 13.00, I would like answer of query to be

id3 | at 12.10 3rd Oct

What is the best query should be? I running the DB on postgresql.

Thanks and appreciated all help.

Answer

You can use date arithmetic:

where createdat >= (date_trunc('day', now()) +
                    (case when extract(hour) from now()) >= 12 then interval '12 hour' else interval '-12 hour' end)
                   )

Advertisement

Answer