Created
July 22, 2013 02:10
-
-
Save mariovisic/6050905 to your computer and use it in GitHub Desktop.
Rails + PostgreSQL: Converting created at date's in the database to Melbourne time.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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"; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment