{"action":"closed","issue":{"url":"https://api.github.com/repos/rails/rails/issues/21126","labels_url":"https://api.github.com/repos/rails/rails/issues/21126/labels{/name}","comments_url":"https://api.github.com/repos/rails/rails/issues/21126/comments","events_url":"https://api.github.com/repos/rails/rails/issues/21126/events","html_url":"https://github.com/rails/rails/issues/21126","id":99019995,"number":21126,"title":"timestamp schema migration on postgres doesn't include timezone","user":{"login":"brodock","id":20575,"avatar_url":"https://avatars.githubusercontent.com/u/20575?v=3","gravatar_id":"","url":"https://api.github.com/users/brodock","html_url":"https://github.com/brodock","followers_url":"https://api.github.com/users/brodock/followers","following_url":"https://api.github.com/users/brodock/following{/other_user}","gists_url":"https://api.github.com/users/brodock/gists{/gist_id}","starred_url":"https://api.github.com/users/brodock/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/brodock/subscriptions","organizations_url":"https://api.github.com/users/brodock/orgs","repos_url":"https://api.github.com/users/brodock/repos","events_url":"https://api.github.com/users/brodock/events{/privacy}","received_events_url":"https://api.github.com/users/brodock/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/rails/rails/labels/stale","name":"stale","color":"444444"}],"state":"closed","locked":false,"assignee":null,"milestone":null,"comments":7,"created_at":"2015-08-04T17:35:19Z","updated_at":"2015-12-23T17:54:55Z","closed_at":"2015-12-23T17:54:55Z","body":"I've come to a very interesting problem that we could do better to prevent others from having the same issue, related to how rails create \"timestamp\" fields on PostgreSQL.\r\n\r\nStandard migrations will generate a \"timestamp without timezone\" field, which should be fine for any non database vendor specific use-cases (rails simple queries).\r\n\r\nThe problem is that if you want to use timezone conversions like \"AT TIME ZONE\" on postgres, you get a very confusing behavior:\r\n\r\n> For timestamp with time zone, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT). An input value that has an explicit time zone specified is converted to UTC using the appropriate offset for that time zone. If no time zone is stated in the input string, then it is assumed to be in the time zone indicated by the system's TimeZone parameter, and is converted to UTC using the offset for the timezone zone.\r\n\r\n> Conversions between timestamp without time zone and timestamp with time zone normally assume that the timestamp without time zone value should be taken or given as timezone local time. A different time zone can be specified for the conversion using AT TIME ZONE.\r\n\r\n> See: 8.5.1.3. Time Stamps at http://www.postgresql.org/docs/9.3/static/datatype-datetime.html\r\n\r\nThis is the expected behavior of AT TIME ZONE:\r\n(it will do the timezone calculations to convert any timestamp data to the one specified).\r\n\r\n```\r\nshow timezone;\r\n TimeZone\r\n----------\r\n UTC\r\n\r\nSELECT NOW(), NOW() at time zone 'America/Sao_Paulo';\r\n             now              |         timezone\r\n------------------------------+---------------------------\r\n 2015-08-04 17:18:32.34449+00 | 2015-08-04 14:18:32.34449\r\n```\r\nFYI: America/Sao_Paulo (BRT) is UTC-3.\r\n\r\nQuerying a recently created model for their \"created_at\" field, and a version using \"AT TIME ZONE\" I get:\r\n\r\n```\r\nSELECT created_at, created_at AT TIME ZONE 'America/Sao_Paulo' AS created_at_timezone FROM \"mytable\" ORDER BY id DESC LIMIT 1;\r\n\r\n    id    |         created_at         |      created_at_timezone\r\n----------+----------------------------+-------------------------------\r\n 36597068 | 2015-08-04 17:26:37.551733 | 2015-08-04 20:26:37.551733+00\r\n\r\n```\r\n\r\nInstead of -3, what I'm observing is a +3.\r\nThis is because \"created_at\" doesn't include the timezone information, and therefore, according to PostgreSQL documentation, it's using server machine timezone for the calculation.\r\n\r\nI can't have access to that server (heroku postgres instance), but I suspect it's using CST (which is UTC-6).\r\n\r\nI can define the timezone using a subquery like the following example:\r\n\r\n```\r\nSELECT t.created_at, t.created_at AT TIME ZONE 'America/Sao_Paulo' AS created_at_timezone FROM (SELECT created_at AT TIME ZONE 'UTC' as created_at FROM \"mytable\" ORDER BY ID desc) as t LIMIT 1;\r\n\r\n          created_at           |    created_at_timezone\r\n-------------------------------+----------------------------\r\n 2015-08-04 17:26:37.551733+00 | 2015-08-04 14:26:37.551733\r\n```\r\n\r\nThere is currently a workaround to create database timestamp with timezone using migrations DSL:\r\nhttp://stackoverflow.com/questions/19207856/railshow-to-create-a-time-column-with-timezone-on-postgres\r\n\r\nWhat I propose is that we create a flag for whether you want to create a timestamp \"with timezone or not\", or default it to create with timezone, as there is no bad side-effects"}}