how to get weekday and weekend count for months?

November 8, 2021

I have the following query where I need to count weekdays and weekends for following months

SELECT 
  fname, 
  MONTH(eventDate), 
  SUM(IF(WEEKDAY(eventDate) < 5,1,0)) AS WeekdayCount,
  SUM(IF(WEEKDAY(eventDate) >= 5,1,0)) AS WeekendCount
FROM eventcal AS e
LEFT JOIN users AS u ON e.primary = u.username
GROUP BY fname, MONTH(eventDate);

But I’m having a problem with timestamp and I’m getting this

(Postgrex.Error) ERROR 42883 (undefined_function) function weekday(timestamp without time zone) does not exist

since I have an event date which is a timestamp for inserted_at. It’s not able to do query on that. What should I do?

does postgres doesn’t have this function?

what can be an alternative to this in Postgres?

>Solution :

Use EXTRACT with the date part isodow. This returns a value of 1 to 7 for Monday through Sunday.

SELECT
    u.fname, 
    MONTH(e.eventDate),
    COUNT(*) FILTER (WHERE EXTRACT(idodow FROM e.eventDate) < 6) AS WeekdayCount,
    COUNT(*) FILTER (WHERE EXTRACT(idodow FROM e.eventDate) IN (6, 7)) AS WeekendCount,
FROM users u
LEFT JOIN eventcal e ON e.primary = u.username
GROUP BY
    u.fname,
    MONTH(e.eventDate);

Note also that a left join from users to the eventcal table would seem to make the most sense here, not the reverse.