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?
CodePudding user response:
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.