Calculating forward dates in PostgreSQL

Question

Got a PostgreSQL query that works well but doesn't seem very efficient...

SELECT
    _id,
    location,
    day,
    title,
    teacher,
    canceled,
    CASE
      WHEN day <= EXTRACT(dow from CURRENT_DATE)::integer THEN CURRENT_DATE + day + (6 - EXTRACT(dow from CURRENT_DATE)::integer) + "startTime"  
      WHEN day > EXTRACT(dow from CURRENT_DATE)::integer THEN CURRENT_DATE + (day - EXTRACT(dow from CURRENT_DATE)::integer - 1) + "startTime"
    END AS "startTime",
    CASE
      WHEN day <= EXTRACT(dow from CURRENT_DATE)::integer THEN CURRENT_DATE + day + (6 - EXTRACT(dow from CURRENT_DATE)::integer) + "endTime"  
      WHEN day > EXTRACT(dow from CURRENT_DATE)::integer THEN CURRENT_DATE + (day - EXTRACT(dow from CURRENT_DATE)::integer - 1) + "endTime"
    END AS "endTime"
    FROM "Schedules"
    ORDER BY location, day, "startTime";

In this case, day column is the day of the week (1-7, not 0-6). It displays dates from the current date onward so if it's Wednesday (day 4), it shows records with day = 4 as the current date, day = 5 is current date + 1, etc. If the day of week is lower, it shows next week's dates.

Would anyone have any suggestions regarding optimizing this?

Add `EXPLAIN ANALYZE` to the start of your query, and save the results in a file. Then do the same after adding an index as IMCoins has suggested. In order to measure improvement, you need a baseline. — Richard, Feb 27 '19 at 21:58
Yep, got an index on day and performance is very good. The main thing that concerned me is the repetition in the CASE portion - so darned ugly. Wondering if there are any more efficient approaches to coming up with the same results. — nstuyvesant, Feb 27 '19 at 22:06

isapir · Answer 1 · 2019-02-27T22:28:33.763

It's difficult to know without looking at your data and indexes, but you can try to calculate the DOW once and use it instead of extracting so many times, which will make your query more efficient and more readable.

e.g.

SELECT your_columns
      ,case when day <= v.curr_dow then 
                 current_day + day + 6 - v.curr_dow + startTime
            else current_day + day - 1 - v.curr_dow + startTime
            end as startTime
      ...
FROM   your_table
      cross join (values(extract(dow from current_date)::integer)) AS v(curr_dow)

bignose · Answer 2 · 2019-02-27T23:21:12.467

0

One improvement is to only compute the current day-of-week one time:

SELECT
    _id,
    location,
    day,
    title,
    teacher,
    canceled,
    CASE
        WHEN day <= current.dow THEN CURRENT_DATE + day + (6 - current.dow) + "startTime"
        WHEN day > current.dow THEN CURRENT_DATE + (day - current.dow - 1) + "startTime"
    END AS "startTime",
    CASE
        WHEN day <= current.dow THEN CURRENT_DATE + day + (6 - current.dow) + "endTime"
        WHEN day > current.dow THEN CURRENT_DATE + (day - current.dow - 1) + "endTime"
    END AS "endTime"
    FROM "Schedules"
        FULL JOIN (
            SELECT EXTRACT(dow from CURRENT_DATE)::integer AS dow
        ) AS current
        ON TRUE
    ORDER BY location, day, "startTime";

edited Feb 27 '19 at 23:21

answered Feb 27 '19 at 22:43

bignose

30,281
14
77
110

Got... ERROR: syntax error at or near "ORDER" LINE 20: ORDER BY location, day, "startTime"; ^ SQL state: 42601 Character: 627 – nstuyvesant Feb 27 '19 at 23:18
@nstuyvesant, thanks! I have added the obligatory join condition (we want the `current` joined to every result row). – bignose Feb 27 '19 at 23:22

Erwin Brandstetter · Accepted Answer · 2019-02-28T01:06:40.353

Add 6, subtract the day of the week dow and take it modulo 7 (% is the modulo operator).
That's the number of days (0-6) to add:

SELECT _id, location, day, title, teacher, canceled
      , now()::date + ((day + 6 - EXTRACT(dow from now())::int) % 7) + "startTime"
      , now()::date + ((day + 6 - EXTRACT(dow from now())::int) % 7) + "endTime"
FROM    "Schedules"
ORDER   BY location, day, "startTime";

SQL in postgres convert datetime for recurring event to future datetime

Or, to avoid repeating the expression (but I doubt it's going to be faster):

SELECT _id, location, day, title, teacher, canceled
     , t.d + "startTime"
     , t.d + "endTime"
FROM   "Schedules" s
, LATERAL (SELECT now()::date + (s.day + 6 - EXTRACT(dow from now())::int) % 7) t(d)
ORDER  BY location, day, "startTime";

What is the difference between LATERAL and a subquery in PostgreSQL?

Calculating forward dates in PostgreSQL

3 Answers3