I have table
Table "public.queue_calendar"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
----------------+------------------------+-----------+----------+---------+----------+-------------+--------------+-------------
day_of_week | character varying(10) | | not null | | extended | | |
time_slot | time without time zone | | not null | | plain | | |
total_slots | integer | | | | plain | | |
occupied_slots | integer | | | | plain | | |
Indexes:
"queue_calendar_pkey" PRIMARY KEY, btree (day_of_week, time_slot)
Access method: heap
with the data (cropped days)
day_of_week | time_slot | total_slots | occupied_slots
-------------+-----------+-------------+----------------
Monday | 08:30:00 | 10 | 0
Monday | 09:00:00 | 10 | 0
Monday | 09:30:00 | 10 | 0
Monday | 10:00:00 | 10 | 0
Monday | 10:30:00 | 10 | 0
Monday | 11:00:00 | 10 | 0
Monday | 11:30:00 | 10 | 0
Monday | 12:00:00 | 10 | 0
Monday | 12:30:00 | 10 | 0
Monday | 13:00:00 | 10 | 0
Monday | 13:30:00 | 10 | 0
Monday | 14:00:00 | 10 | 0
Monday | 14:30:00 | 10 | 0
Monday | 15:00:00 | 10 | 0
Monday | 15:30:00 | 10 | 0
Monday | 16:00:00 | 10 | 0
Monday | 16:30:00 | 10 | 0
Monday | 17:00:00 | 10 | 0
Monday | 17:30:00 | 10 | 0
Monday | 18:00:00 | 10 | 0
Monday | 18:30:00 | 10 | 0
Monday | 19:00:00 | 10 | 0
Monday | 19:30:00 | 10 | 0
Monday | 20:00:00 | 10 | 0
Monday | 20:30:00 | 10 | 0
Tuesday | 08:30:00 | 10 | 0
Tuesday | 09:00:00 | 10 | 0
Tuesday | 09:30:00 | 10 | 0
Tuesday | 10:00:00 | 10 | 0
Tuesday | 10:30:00 | 10 | 0
Tuesday | 11:00:00 | 10 | 0
Tuesday | 11:30:00 | 10 | 0
Tuesday | 12:00:00 | 10 | 0
Tuesday | 12:30:00 | 10 | 0
Tuesday | 13:00:00 | 10 | 0
Tuesday | 13:30:00 | 10 | 0
Tuesday | 14:00:00 | 10 | 0
Tuesday | 14:30:00 | 10 | 0
Tuesday | 15:00:00 | 10 | 0
Tuesday | 15:30:00 | 10 | 0
Tuesday | 16:00:00 | 10 | 0
Tuesday | 16:30:00 | 10 | 0
Tuesday | 17:00:00 | 10 | 0
Tuesday | 17:30:00 | 10 | 0
Tuesday | 18:00:00 | 10 | 0
Tuesday | 18:30:00 | 10 | 0
Tuesday | 19:00:00 | 10 | 0
Tuesday | 19:30:00 | 10 | 0
Tuesday | 20:00:00 | 10 | 0
Tuesday | 20:30:00 | 10 | 0
I have the query which extract all data for the current day where occupied_slots < total_slots (this one is work correctly) but when I try to make statement when current time is less than 12:00 - I should extract data for the NEXT day from 08:30
the data extracted but I don't understand why it was extracted from 10:00 (not from 08:30)
here is the SELECT statement
WITH current_time_slot AS (
SELECT
CASE
WHEN EXTRACT(MINUTE FROM CURRENT_TIME + interval '2 hour') BETWEEN 0 AND 10 THEN
DATE_TRUNC('HOUR', CURRENT_TIMESTAMP + interval '2 hour')
WHEN EXTRACT(MINUTE FROM CURRENT_TIME + interval '2 hour') BETWEEN 11 AND 29 THEN
DATE_TRUNC('HOUR', CURRENT_TIMESTAMP + interval '2 hour') + INTERVAL '30 minutes'
WHEN EXTRACT(MINUTE FROM CURRENT_TIME + interval '2 hour') BETWEEN 30 AND 40 THEN
DATE_TRUNC('HOUR', CURRENT_TIMESTAMP + interval '2 hour')
WHEN EXTRACT(MINUTE FROM CURRENT_TIME + interval '2 hour') BETWEEN 41 AND 59 THEN
DATE_TRUNC('HOUR', CURRENT_TIMESTAMP + interval '2 hour') + INTERVAL '30 minutes'
ELSE
NULL
END AS start_time
)
SELECT
day_of_week,
TO_CHAR(time_slot, 'HH24:MI') AS time_slot,
total_slots,
occupied_slots
FROM
(
SELECT
day_of_week,
time_slot,
LEAD(time_slot) OVER (PARTITION BY day_of_week ORDER BY time_slot) AS next_time_slot,
total_slots,
occupied_slots
FROM
queue_calendar,
current_time_slot
WHERE
(
EXTRACT(DOW FROM CURRENT_TIMESTAMP) =
CASE
WHEN CURRENT_TIME + interval '2 hour' < '12:00' THEN
CASE
WHEN day_of_week = 'Monday' THEN 1
WHEN day_of_week = 'Tuesday' THEN 2
WHEN day_of_week = 'Wednesday' THEN 3
WHEN day_of_week = 'Thursday' THEN 4
WHEN day_of_week = 'Friday' THEN 5
WHEN day_of_week = 'Saturday' THEN 6
WHEN day_of_week = 'Sunday' THEN 0
END
ELSE
CASE
WHEN day_of_week = 'Monday' THEN 0
WHEN day_of_week = 'Tuesday' THEN 1
WHEN day_of_week = 'Wednesday' THEN 2
WHEN day_of_week = 'Thursday' THEN 3
WHEN day_of_week = 'Friday' THEN 4
WHEN day_of_week = 'Saturday' THEN 5
WHEN day_of_week = 'Sunday' THEN 6
END
END
)
AND (
(
(CURRENT_TIME + interval '2 hour' < '12:00' AND time_slot >= current_time_slot.start_time::TIME)
OR
(CURRENT_TIME + interval '2 hour' >= '12:00' AND time_slot >= '08:30')
)
AND occupied_slots < total_slots + 2
)
) AS subquery
WHERE
CURRENT_TIME < next_time_slot OR next_time_slot IS NULL
ORDER BY
day_of_week, time_slot;
could someone point me to the right direction to fix this
PS: here is the response
day_of_week | time_slot | total_slots | occupied_slots
-------------+-----------+-------------+----------------
Tuesday | 10:30 | 10 | 0
Tuesday | 11:00 | 10 | 0
Tuesday | 11:30 | 10 | 0
Tuesday | 12:00 | 10 | 0
Tuesday | 12:30 | 10 | 0
Tuesday | 13:00 | 10 | 0
Tuesday | 13:30 | 10 | 0
Tuesday | 14:00 | 10 | 0
Tuesday | 14:30 | 10 | 0
Tuesday | 15:00 | 10 | 0
Tuesday | 15:30 | 10 | 0
Tuesday | 16:00 | 10 | 0
Tuesday | 16:30 | 10 | 0
Tuesday | 17:00 | 10 | 0
Tuesday | 17:30 | 10 | 0
Tuesday | 18:00 | 10 | 0
Tuesday | 18:30 | 10 | 0
Tuesday | 19:00 | 10 | 0
Tuesday | 19:30 | 10 | 0
Tuesday | 20:00 | 10 | 0
Tuesday | 20:30 | 10 | 0
(21 rows)
fixed WHERE statement
from
to