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?
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.