Postgresql: Select between dates + time

Question

Postgresql: Select between dates + time

Asked 3 years, 9 months ago

Viewed 150 times

0

Good morning. In the movement table, I have, among others, the date and time fields. I need to select between the chosen date and the following day (Ex.: between 09/11/2020 and 09/12/2020). I need the select Filtre between '07:00' of the day 11/09/2020 and '07:00' of the day 12/09/2020, informing the movements 24 hours.

Between dates, manually informing, ok:

select * from movimento
WHERE '[2020-09-11, 2020-09-12]'::daterange @> data
order by data,hora

However, I would like, tbm, to automatically pick up the day after the first informed day. In addition to the time filters. To know: In the system, the time is entered manually during the registration as it can be done outside the current time.

Postgresql in particular provides the following special date values (date and timestamp): today, tomorrow and yesterday considering, for timestamp, the time (00:00).

– anonimo

2020/09/11 at 13:51
Assuming your date and time fields are of the type date and time use: WHERE (data+hora) - INTERVAL '7 HOUR' BETWEEN 'today'::timestamp AND 'tomorrow'::timestamp;

– anonimo

2020/09/11 at 14:02
I assumed that data be the type date and hora of the kind time. Here is a test: bdteste=# SELECT '2020-09-11 12:00:00'::TIMESTAMP - INTERVAL '7 HOUR' BETWEEN 'today'::timestamp AND 'tomorrow'::timestamp;
 ?column? 
----------
 t
(1 row)

– anonimo

2020/09/11 at 16:19
Thanks anonymity. I did as you suggest, but the return brings all the records. I need all records to appear between the chosen date (09/11/2020) and your time (set at 07:00:00 in the morning) and your next day (09/12/2020 07:00:00). Totaling 24 hours of movements.

– Jones713

2020/09/11 at 16:38
My test worked as expected: SELECT * FROM (VALUES ('2020-09-09', '10:00'), ('2020-09-10', '03:00'), ('2020-09-11', '12:00'), ('2020-09-12', '05:00'), ('2020-09-12', '18:00')) AS t(data, hora) WHERE (data::DATE + hora::TIME) - INTERVAL '7 HOUR' BETWEEN 'today'::timestamp AND 'tomorrow'::timestamp;
 data | hora 
------------+-------
 2020-09-11 | 12:00
 2020-09-12 | 05:00
(2 rows)

– anonimo

2020/09/11 at 17:52
I managed to follow the anonymous tip. Thanks for the information.

– Jones713

2020/09/11 at 18:22

Show 1 more comment

1 answer

Browser other questions tagged postgresql delphi-7

You are not signed in. Login or sign up in order to post.

by Marcos Alexandre • **1,284** points · Answer 1 · 2020-09-11T12:55:04+00:00

Hello,

From what I understand, you give a date from which you want the interval of 24h (or 1) day after the chosen time.

Try to use the interval:

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 is everything that postgresql offers in relation to this Documentation

PS: Don’t forget to tell me whether or not he answers you.