{"action":"created","issue":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/9596","repository_url":"https://api.github.com/repos/cockroachdb/cockroach","labels_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/9596/labels{/name}","comments_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/9596/comments","events_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/9596/events","html_url":"https://github.com/cockroachdb/cockroach/issues/9596","id":179814096,"number":9596,"title":"sql: [strawman] simplify date/time/timestamp handling by ignoring session offset","user":{"login":"dt","id":15615,"avatar_url":"https://avatars.githubusercontent.com/u/15615?v=3","gravatar_id":"","url":"https://api.github.com/users/dt","html_url":"https://github.com/dt","followers_url":"https://api.github.com/users/dt/followers","following_url":"https://api.github.com/users/dt/following{/other_user}","gists_url":"https://api.github.com/users/dt/gists{/gist_id}","starred_url":"https://api.github.com/users/dt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dt/subscriptions","organizations_url":"https://api.github.com/users/dt/orgs","repos_url":"https://api.github.com/users/dt/repos","events_url":"https://api.github.com/users/dt/events{/privacy}","received_events_url":"https://api.github.com/users/dt/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":{"login":"dt","id":15615,"avatar_url":"https://avatars.githubusercontent.com/u/15615?v=3","gravatar_id":"","url":"https://api.github.com/users/dt","html_url":"https://github.com/dt","followers_url":"https://api.github.com/users/dt/followers","following_url":"https://api.github.com/users/dt/following{/other_user}","gists_url":"https://api.github.com/users/dt/gists{/gist_id}","starred_url":"https://api.github.com/users/dt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dt/subscriptions","organizations_url":"https://api.github.com/users/dt/orgs","repos_url":"https://api.github.com/users/dt/repos","events_url":"https://api.github.com/users/dt/events{/privacy}","received_events_url":"https://api.github.com/users/dt/received_events","type":"User","site_admin":false},"assignees":[{"login":"dt","id":15615,"avatar_url":"https://avatars.githubusercontent.com/u/15615?v=3","gravatar_id":"","url":"https://api.github.com/users/dt","html_url":"https://github.com/dt","followers_url":"https://api.github.com/users/dt/followers","following_url":"https://api.github.com/users/dt/following{/other_user}","gists_url":"https://api.github.com/users/dt/gists{/gist_id}","starred_url":"https://api.github.com/users/dt/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dt/subscriptions","organizations_url":"https://api.github.com/users/dt/orgs","repos_url":"https://api.github.com/users/dt/repos","events_url":"https://api.github.com/users/dt/events{/privacy}","received_events_url":"https://api.github.com/users/dt/received_events","type":"User","site_admin":false},{"login":"mjibson","id":41181,"avatar_url":"https://avatars.githubusercontent.com/u/41181?v=3","gravatar_id":"","url":"https://api.github.com/users/mjibson","html_url":"https://github.com/mjibson","followers_url":"https://api.github.com/users/mjibson/followers","following_url":"https://api.github.com/users/mjibson/following{/other_user}","gists_url":"https://api.github.com/users/mjibson/gists{/gist_id}","starred_url":"https://api.github.com/users/mjibson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mjibson/subscriptions","organizations_url":"https://api.github.com/users/mjibson/orgs","repos_url":"https://api.github.com/users/mjibson/repos","events_url":"https://api.github.com/users/mjibson/events{/privacy}","received_events_url":"https://api.github.com/users/mjibson/received_events","type":"User","site_admin":false},{"login":"paperstreet","id":52528,"avatar_url":"https://avatars.githubusercontent.com/u/52528?v=3","gravatar_id":"","url":"https://api.github.com/users/paperstreet","html_url":"https://github.com/paperstreet","followers_url":"https://api.github.com/users/paperstreet/followers","following_url":"https://api.github.com/users/paperstreet/following{/other_user}","gists_url":"https://api.github.com/users/paperstreet/gists{/gist_id}","starred_url":"https://api.github.com/users/paperstreet/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/paperstreet/subscriptions","organizations_url":"https://api.github.com/users/paperstreet/orgs","repos_url":"https://api.github.com/users/paperstreet/repos","events_url":"https://api.github.com/users/paperstreet/events{/privacy}","received_events_url":"https://api.github.com/users/paperstreet/received_events","type":"User","site_admin":false},{"login":"bdarnell","id":160562,"avatar_url":"https://avatars.githubusercontent.com/u/160562?v=3","gravatar_id":"","url":"https://api.github.com/users/bdarnell","html_url":"https://github.com/bdarnell","followers_url":"https://api.github.com/users/bdarnell/followers","following_url":"https://api.github.com/users/bdarnell/following{/other_user}","gists_url":"https://api.github.com/users/bdarnell/gists{/gist_id}","starred_url":"https://api.github.com/users/bdarnell/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/bdarnell/subscriptions","organizations_url":"https://api.github.com/users/bdarnell/orgs","repos_url":"https://api.github.com/users/bdarnell/repos","events_url":"https://api.github.com/users/bdarnell/events{/privacy}","received_events_url":"https://api.github.com/users/bdarnell/received_events","type":"User","site_admin":false},{"login":"petermattis","id":6201335,"avatar_url":"https://avatars.githubusercontent.com/u/6201335?v=3","gravatar_id":"","url":"https://api.github.com/users/petermattis","html_url":"https://github.com/petermattis","followers_url":"https://api.github.com/users/petermattis/followers","following_url":"https://api.github.com/users/petermattis/following{/other_user}","gists_url":"https://api.github.com/users/petermattis/gists{/gist_id}","starred_url":"https://api.github.com/users/petermattis/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/petermattis/subscriptions","organizations_url":"https://api.github.com/users/petermattis/orgs","repos_url":"https://api.github.com/users/petermattis/repos","events_url":"https://api.github.com/users/petermattis/events{/privacy}","received_events_url":"https://api.github.com/users/petermattis/received_events","type":"User","site_admin":false},{"login":"vivekmenezes","id":11774212,"avatar_url":"https://avatars.githubusercontent.com/u/11774212?v=3","gravatar_id":"","url":"https://api.github.com/users/vivekmenezes","html_url":"https://github.com/vivekmenezes","followers_url":"https://api.github.com/users/vivekmenezes/followers","following_url":"https://api.github.com/users/vivekmenezes/following{/other_user}","gists_url":"https://api.github.com/users/vivekmenezes/gists{/gist_id}","starred_url":"https://api.github.com/users/vivekmenezes/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vivekmenezes/subscriptions","organizations_url":"https://api.github.com/users/vivekmenezes/orgs","repos_url":"https://api.github.com/users/vivekmenezes/repos","events_url":"https://api.github.com/users/vivekmenezes/events{/privacy}","received_events_url":"https://api.github.com/users/vivekmenezes/received_events","type":"User","site_admin":false}],"milestone":null,"comments":2,"created_at":"2016-09-28T15:57:42Z","updated_at":"2016-09-28T16:03:58Z","closed_at":null,"body":"[moving an offline discussion of \"is this even worth spending time on?\" here for broader participation]\r\n\r\n## TL;DR\r\nDisallow non-zero session zone offsets, always render timestamps in UTC and with zero offset.\r\n## Summary\r\nSession time zone offsets are very subtle and potentially confusing -- when and where they are (or should be) used or not has proven unintuitive and consumed a disproportionate amount of time and energy. Rendering times in a particular offset (which is basically all it is used for) is primarily a application/presentation layer concern, and we might be best off leaving it to applications and focusing elsewhere for now.\r\n\r\n## Background\r\nThe SQL spec (and postgres) says that a `timestamp` is the same as a `timestamp without time zone`. The difference between `with time zone` and `without time zone` timestamps is how they are rendered to the client -- `with time zone` is rendered _in the session offset_, with an offset string suffix reflecting that, while `timestamp (without time zone)` is rendered as-is, with no offset. Additionally, the session offset is used if an offset is not specified when parsing a `with time zone` timestamp from a string.\r\n\r\n## Problems\r\nSubtlety arises when casting between these or to and from other types, comparing them, etc e.g. casting a `timestamp with time zone` to a `date` returns different values based on the session offset while casting a `timestamp` to a `date` does not.\r\n\r\nAssuming that most production applications support users in multiple time zones and thus localize their times in a rendering and presentation layer (based only on anecdotal experience) rather than at the database connection layer, it seems that the the benefit of supporting non-zero session offsets may not justify the effort required to maintain them code and documentation complexity at this time.\r\n"},"comment":{"url":"https://api.github.com/repos/cockroachdb/cockroach/issues/comments/250213547","html_url":"https://github.com/cockroachdb/cockroach/issues/9596#issuecomment-250213547","issue_url":"https://api.github.com/repos/cockroachdb/cockroach/issues/9596","id":250213547,"user":{"login":"mjibson","id":41181,"avatar_url":"https://avatars.githubusercontent.com/u/41181?v=3","gravatar_id":"","url":"https://api.github.com/users/mjibson","html_url":"https://github.com/mjibson","followers_url":"https://api.github.com/users/mjibson/followers","following_url":"https://api.github.com/users/mjibson/following{/other_user}","gists_url":"https://api.github.com/users/mjibson/gists{/gist_id}","starred_url":"https://api.github.com/users/mjibson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/mjibson/subscriptions","organizations_url":"https://api.github.com/users/mjibson/orgs","repos_url":"https://api.github.com/users/mjibson/repos","events_url":"https://api.github.com/users/mjibson/events{/privacy}","received_events_url":"https://api.github.com/users/mjibson/received_events","type":"User","site_admin":false},"created_at":"2016-09-28T16:03:58Z","updated_at":"2016-09-28T16:03:58Z","body":"That is, parsing is exactly the same for both with and without zone: it uses the session if no zone specified in the string. The only difference is in rendering."}}
{"actor":{"display_login":"mjibson"}}