Query postgres database

Question

I am doing the function of booking a hotel, when I get the rooms available by date by following

SELECT * 
FROM oder 
WHERE
    startDate >= '2020-12-26 06:53:18.114+00' AND 
    endDate <= '2020-12-30 06:53:18.114+00'

but it works when i enter the exact start and end date stored in the database, i want when i enter the start and end date time can be within the period stored in the database, I can also get the room that was booked.

example: I choose the startDate = 2020-12-27 and the endDate=2020-12-28is within the period of 2020-12-26 to 2020-12-30, I want the result to return like the above query

SELECT * 
FROM oder 
WHERE
    startDate >= '2020-12-26 06:53:18.114+00' AND
    endDate <= '2020-12-28 06:53:18.114+00'

Please add some sample data and the expected output. Incl. some data which wouldn't fit the condition. — S-Man
– S-Man, Commented Jan 7, 2021 at 13:57

Frank Heikens · Accepted Answer · 2021-01-07 16:06:21Z

1

Looks like you might be interested in a range datatype, tsrange in this case. When using a range, you can also create a constraint to avoid double bookings.

The various range functions will help you to find the records you're looking for.

edit: When using a range and looking for records that overlap, this query might do the job:

SELECT
    * 
FROM
    oder 
WHERE
    tsrange_field && tsrange ( '2020-12-26 06:53:18.114+00', '2020-12-28 06:53:18.114+00', '[]' );

Assumption: Field tsrange_field will be the field where you store your timestamp ranges.

edited Jan 7, 2021 at 16:06

answered Jan 7, 2021 at 13:56

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Popeye · Accepted Answer · 2021-01-07 14:09:41Z

0

If I followed you correctly, You need the rooms booked for the period that is given by you as an input.

You can use the OR condition and BETWEEN as follows:

SELECT * 
  FROM oder 
 WHERE startDate between '2020-12-26 06:53:18.114+00' AND '2020-12-30 06:53:18.114+00'
    or endDate between '2020-12-26 06:53:18.114+00' AND '2020-12-30 06:53:18.114+00'

answered Jan 7, 2021 at 14:09

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Query postgres database

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related