Plus, skipping the key word AS is fine for table aliases but not for column aliases. If you GROUP BY day and month only, you can easily aggregate dates from multiple years by accident. Safe syntax would be: to_char(applied_date AT TIME ZONE 'America/New_York', 'MM-DD') - your time zone
Accounting for DST in Postgres, when selecting scheduled items.Ignoring timezones altogether in Rails and PostgreSQL.To get the date for a particular time zone, you have to declare that, or the current setting will be applied - which is easily overlooked and therefore a loaded footgun: You would get different numbers, depending on where you run the query! Your sample displays timestamp with time zone literals, which indicates you may be operating in multiple distinct time zones. The extracted day depends on the time zone setting of the current session. ORDER BY (applied_date AT TIME ZONE 'America/New_York')::date) ,sum(count(id)) OVER (PARTITION BY applied_class $$SELECT (applied_date AT TIME ZONE 'America/New_York')::date AS d In addition to what Colin already cleared up: SELECT * FROM crosstab( How can I change this query so that it aggregates the applied_class records by city, instead of adding the previous records from all cities? This query is very close, but it's increasing the count for each applied_class whenever there is a record for any applied_class: Results Day | Boston | New York | Chicago | Any Class To_char(date_trunc($$day$$, applied_date), $$MM-DD$$) d from application_app I'm trying to use crosstab to get an aggregate of applied_class over time (by day). Table: application_app applied_date | applied_class | id