Rails + PostgreSQL: Converting created at date's in the database to Melbourne time.

timezone_convert.sql

-- Rails doesn't actually store timestamps in the database as UTC, it actually
-- stores them without any timezone information in the database (but in UTC
-- time). Which means that to convert a timestamp to another timezone we
-- first need to cast the timestamp to a timestamp that's in UTC and then
-- convert to the timezone we actually want. Melbourne in this case.

SELECT
  TO_CHAR(((created_at AT TIME ZONE 'UTC') AT TIME ZONE 'Australia/Melbourne')::date, 'YYYY-MM-DD') AS "Day", 
FROM jobs
GROUP BY "Day";