{"action":"created","issue":{"url":"https://api.github.com/repos/hammerlab/cycledash/issues/576","labels_url":"https://api.github.com/repos/hammerlab/cycledash/issues/576/labels{/name}","comments_url":"https://api.github.com/repos/hammerlab/cycledash/issues/576/comments","events_url":"https://api.github.com/repos/hammerlab/cycledash/issues/576/events","html_url":"https://github.com/hammerlab/cycledash/pull/576","id":66005789,"number":576,"title":"Fix timezones","user":{"login":"tavinathanson","id":322932,"avatar_url":"https://avatars.githubusercontent.com/u/322932?v=3","gravatar_id":"","url":"https://api.github.com/users/tavinathanson","html_url":"https://github.com/tavinathanson","followers_url":"https://api.github.com/users/tavinathanson/followers","following_url":"https://api.github.com/users/tavinathanson/following{/other_user}","gists_url":"https://api.github.com/users/tavinathanson/gists{/gist_id}","starred_url":"https://api.github.com/users/tavinathanson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tavinathanson/subscriptions","organizations_url":"https://api.github.com/users/tavinathanson/orgs","repos_url":"https://api.github.com/users/tavinathanson/repos","events_url":"https://api.github.com/users/tavinathanson/events{/privacy}","received_events_url":"https://api.github.com/users/tavinathanson/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"danvk","id":98301,"avatar_url":"https://avatars.githubusercontent.com/u/98301?v=3","gravatar_id":"","url":"https://api.github.com/users/danvk","html_url":"https://github.com/danvk","followers_url":"https://api.github.com/users/danvk/followers","following_url":"https://api.github.com/users/danvk/following{/other_user}","gists_url":"https://api.github.com/users/danvk/gists{/gist_id}","starred_url":"https://api.github.com/users/danvk/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/danvk/subscriptions","organizations_url":"https://api.github.com/users/danvk/orgs","repos_url":"https://api.github.com/users/danvk/repos","events_url":"https://api.github.com/users/danvk/events{/privacy}","received_events_url":"https://api.github.com/users/danvk/received_events","type":"User","site_admin":false},"milestone":null,"comments":5,"created_at":"2015-04-02T20:29:47Z","updated_at":"2015-04-03T20:53:57Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/hammerlab/cycledash/pulls/576","html_url":"https://github.com/hammerlab/cycledash/pull/576","diff_url":"https://github.com/hammerlab/cycledash/pull/576.diff","patch_url":"https://github.com/hammerlab/cycledash/pull/576.patch"},"body":"From http://www.postgresql.org/docs/9.1/static/datatype-datetime.html: \"For `timestamp with time zone`, the internally stored value is always in UTC (Universal Coordinated Time, traditionally known as Greenwich Mean Time, GMT).\"\r\n\r\nVersus, for `timestamp without time zone`: \"a literal that has been determined to be `timestamp without time zone`, PostgreSQL will silently ignore any time zone indication. That is, the resulting value is derived from the date/time fields in the input value, and is not adjusted for time zone.\"\r\n\r\nOther information seems to agree with just using `timestamp with time zone` (`timestampz`): http://justatheory.com/computers/databases/postgresql/use-timestamptz.html\r\n\r\nSo, we now use `timestamp with time zone` + set UTC in the DB's conf. (Though, that UTC setting in the conf file doesn't appear to be necessary, per: \"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\".) Then, moment takes that UTC timestamp and displays the localized version of it. No offsets necessary.\r\n\r\n@danvk regarding microsecond precision (when, according to the documentation, there don't appear to be enough digits to support that): \"timestamp values are stored as seconds before or after midnight 2000-01-01. When timestamp values are implemented using floating-point numbers, microsecond precision is achieved for dates within a few years of 2000-01-01, but the precision degrades for dates further away. Note that using floating-point datetimes allows a larger range of timestamp values to be represented than shown above: from 4713 BC up to 5874897 AD.\"\r\n\r\nMigration code:\r\n\r\n```\r\nalter table user_comments alter column created type timestamp with time zone;\r\nalter table user_comments alter column last_modified type timestamp with time zone;\r\n```\n\n<!-- Reviewable:start -->\n[<img src=\"https://reviewable.io/review_button.png\" height=40 alt=\"Review on Reviewable\"/>](https://reviewable.io/reviews/hammerlab/cycledash/576)\n<!-- Reviewable:end -->\n"},"comment":{"url":"https://api.github.com/repos/hammerlab/cycledash/issues/comments/89416818","html_url":"https://github.com/hammerlab/cycledash/pull/576#issuecomment-89416818","issue_url":"https://api.github.com/repos/hammerlab/cycledash/issues/576","id":89416818,"user":{"login":"tavinathanson","id":322932,"avatar_url":"https://avatars.githubusercontent.com/u/322932?v=3","gravatar_id":"","url":"https://api.github.com/users/tavinathanson","html_url":"https://github.com/tavinathanson","followers_url":"https://api.github.com/users/tavinathanson/followers","following_url":"https://api.github.com/users/tavinathanson/following{/other_user}","gists_url":"https://api.github.com/users/tavinathanson/gists{/gist_id}","starred_url":"https://api.github.com/users/tavinathanson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tavinathanson/subscriptions","organizations_url":"https://api.github.com/users/tavinathanson/orgs","repos_url":"https://api.github.com/users/tavinathanson/repos","events_url":"https://api.github.com/users/tavinathanson/events{/privacy}","received_events_url":"https://api.github.com/users/tavinathanson/received_events","type":"User","site_admin":false},"created_at":"2015-04-03T20:53:57Z","updated_at":"2015-04-03T20:53:57Z","body":"I went ahead and made `created`/`last_modified` both seconds (.microseconds) since epoch. As you suggested, I was able to use the SQL itself to get the epoch (though it involved a bit of SQLalchemy column set fiddling). Overall, I think `comments.py` is simpler now (without the need for any of the date_as_int stuff).\n\nOn the JS side, `created` and `last_modified` are now both suffixed with `_timestamp`.\n\n---\n\n\n\n---\n\n\n\n---\n\nComments from the [review on Reviewable.io](https://reviewable.io:443/reviews/hammerlab/cycledash/576)\n<!-- Sent from Reviewable.io -->\n"}}