sqlpostgresqlsimplifycross-joinlateral

postgresql working hours simplify query


I need a help with some query simplification (like to avoid repetition etc.)

http://www.sqlfiddle.com/#!17/3607d/1/0

We have 2 objects:

Need to simplify a query that finds if that office is working NOW, if office does not have working hours specified, then it is open 24/7.

SELECT
name,
   (CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time,
(workinghours.id IS NULL) OR ((
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN mon_from AND mon_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 1) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN tue_from AND tue_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 2) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN wed_from AND wed_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 3) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN thu_from AND thu_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 4) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN fri_from AND fri_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 5) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN sat_from AND sat_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 6) OR
((CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time BETWEEN sun_from AND sun_to 
 AND EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone) = 7)
) IS True) AS in_workinghours
FROM office
LEFT JOIN workinghours ON (workinghours.id = office.workinghours_id)

Solution

  • SELECT
      name,
      today_time,
      (workinghours.id IS NULL) OR (today_time BETWEEN today_wtime[1] AND today_wtime[2]) AS in_workinghours
    FROM office
    LEFT JOIN workinghours ON (workinghours.id = office.workinghours_id)
    CROSS JOIN LATERAL (
      SELECT
        (CURRENT_TIMESTAMP::time AT TIME ZONE timezone)::time AS today_time,
        CASE EXTRACT(isodow FROM CURRENT_TIMESTAMP AT TIME ZONE timezone)
          WHEN 1 THEN ARRAY[mon_from, mon_to]
          WHEN 2 THEN ARRAY[tue_from, tue_to]
          WHEN 3 THEN ARRAY[wed_from, wed_to]
          WHEN 4 THEN ARRAY[thu_from, thu_to]
          WHEN 5 THEN ARRAY[fri_from, fri_to]
          WHEN 6 THEN ARRAY[sat_from, sat_to]
          WHEN 7 THEN ARRAY[sun_from, sun_to]
        END AS today_wtime) as wtime