PostgreSQL: Select between dates + time
Good Morning. In the movement table, I have, in addition to others, the date and time fields. I need to make a select between the chosen date and its later day (Ex.: between 11/09/2020 and 12/09/2020). I need select to filter between the' 07:00 'of the day 11/09/2020 and the' 07:00 ' of the day 12/09/2020, informing the movements of 24 hours.
Between dates, manually entering, ok:
select * from movimento
WHERE '[2020-09-11, 2020-09-12]'::daterange @> data
order by data,hora
However wanted, tbm, to automatically pick up the day after the first day reported. In addition to time filters. Namely: in the system, the time is reported manually during registration as it can be carried out outside the current time.
1 answers
Hello,
As I understand it, you enter a date from which you want the interval of 24h (or 1) day after the chosen time.
Try using the range:
select *
from movimentos
where true
-- obeservar que o valor da data '2020-09-11 07:00:00' deve vir de alguma variável que é utilizada duas vezes
and (data + hora)::timestamp between '2020-09-11 07:00:00' and ('2020-09-11 07:00:00'::timestamp + interval '1 day')
order by
data,
hora
Take a look here too. example
Here's everything postgresql offers regarding this documentation
PS: don't forget to tell if it suits you or not.