{"action":"created","issue":{"url":"https://api.github.com/repos/metabase/metabase/issues/2517","repository_url":"https://api.github.com/repos/metabase/metabase","labels_url":"https://api.github.com/repos/metabase/metabase/issues/2517/labels{/name}","comments_url":"https://api.github.com/repos/metabase/metabase/issues/2517/comments","events_url":"https://api.github.com/repos/metabase/metabase/issues/2517/events","html_url":"https://github.com/metabase/metabase/issues/2517","id":152733267,"number":2517,"title":"Week numbers are off by 1","user":{"login":"whatthewhat","id":411554,"avatar_url":"https://avatars.githubusercontent.com/u/411554?v=3","gravatar_id":"","url":"https://api.github.com/users/whatthewhat","html_url":"https://github.com/whatthewhat","followers_url":"https://api.github.com/users/whatthewhat/followers","following_url":"https://api.github.com/users/whatthewhat/following{/other_user}","gists_url":"https://api.github.com/users/whatthewhat/gists{/gist_id}","starred_url":"https://api.github.com/users/whatthewhat/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/whatthewhat/subscriptions","organizations_url":"https://api.github.com/users/whatthewhat/orgs","repos_url":"https://api.github.com/users/whatthewhat/repos","events_url":"https://api.github.com/users/whatthewhat/events{/privacy}","received_events_url":"https://api.github.com/users/whatthewhat/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/metabase/metabase/labels/Correctness","name":"Correctness","color":"e11d21"},{"url":"https://api.github.com/repos/metabase/metabase/labels/Timezones","name":"Timezones","color":"9E2E67"},{"url":"https://api.github.com/repos/metabase/metabase/labels/Visualization","name":"Visualization","color":"95EBFC"}],"state":"open","locked":false,"assignee":{"login":"tlrobinson","id":18193,"avatar_url":"https://avatars.githubusercontent.com/u/18193?v=3","gravatar_id":"","url":"https://api.github.com/users/tlrobinson","html_url":"https://github.com/tlrobinson","followers_url":"https://api.github.com/users/tlrobinson/followers","following_url":"https://api.github.com/users/tlrobinson/following{/other_user}","gists_url":"https://api.github.com/users/tlrobinson/gists{/gist_id}","starred_url":"https://api.github.com/users/tlrobinson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tlrobinson/subscriptions","organizations_url":"https://api.github.com/users/tlrobinson/orgs","repos_url":"https://api.github.com/users/tlrobinson/repos","events_url":"https://api.github.com/users/tlrobinson/events{/privacy}","received_events_url":"https://api.github.com/users/tlrobinson/received_events","type":"User","site_admin":false},"assignees":[{"login":"tlrobinson","id":18193,"avatar_url":"https://avatars.githubusercontent.com/u/18193?v=3","gravatar_id":"","url":"https://api.github.com/users/tlrobinson","html_url":"https://github.com/tlrobinson","followers_url":"https://api.github.com/users/tlrobinson/followers","following_url":"https://api.github.com/users/tlrobinson/following{/other_user}","gists_url":"https://api.github.com/users/tlrobinson/gists{/gist_id}","starred_url":"https://api.github.com/users/tlrobinson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tlrobinson/subscriptions","organizations_url":"https://api.github.com/users/tlrobinson/orgs","repos_url":"https://api.github.com/users/tlrobinson/repos","events_url":"https://api.github.com/users/tlrobinson/events{/privacy}","received_events_url":"https://api.github.com/users/tlrobinson/received_events","type":"User","site_admin":false}],"milestone":{"url":"https://api.github.com/repos/metabase/metabase/milestones/25","html_url":"https://github.com/metabase/metabase/milestones/0.21.0","labels_url":"https://api.github.com/repos/metabase/metabase/milestones/25/labels","id":2008124,"number":25,"title":"0.21.0","description":"Code complete: Nov 17th\r\nRelease date: Nov 19th","creator":{"login":"salsakran","id":107439,"avatar_url":"https://avatars.githubusercontent.com/u/107439?v=3","gravatar_id":"","url":"https://api.github.com/users/salsakran","html_url":"https://github.com/salsakran","followers_url":"https://api.github.com/users/salsakran/followers","following_url":"https://api.github.com/users/salsakran/following{/other_user}","gists_url":"https://api.github.com/users/salsakran/gists{/gist_id}","starred_url":"https://api.github.com/users/salsakran/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/salsakran/subscriptions","organizations_url":"https://api.github.com/users/salsakran/orgs","repos_url":"https://api.github.com/users/salsakran/repos","events_url":"https://api.github.com/users/salsakran/events{/privacy}","received_events_url":"https://api.github.com/users/salsakran/received_events","type":"User","site_admin":false},"open_issues":26,"closed_issues":2,"state":"open","created_at":"2016-09-15T23:59:31Z","updated_at":"2016-10-18T19:23:08Z","due_on":"2016-11-19T00:00:00Z","closed_at":null},"comments":6,"created_at":"2016-05-03T09:28:43Z","updated_at":"2016-10-18T20:15:15Z","closed_at":null,"body":"When creating queries grouped by week the week numbers seems to be off by 1 (e.g. current week is shown as 19, but should be 18).\r\n\r\nWhen comparing with the json data returned it looks like this might be a timezone issue, but changing \"REPORT TIMEZONE\" is settings has no effect:\r\n\r\n![](https://s3.amazonaws.com/f.cl.ly/items/3S0P1U1Q2K0F3c1C2z43/Image%202016-05-03%20at%2012.20.21%20PM.png?v=b7723428)\r\n\r\n![](https://s3.amazonaws.com/f.cl.ly/items/0B3c2K2d0k3d1u1I2t3U/Image%202016-05-03%20at%2012.21.11%20PM.png?v=9efb85d9)\r\n\r\nTested on browsers with UTC+3 and UTC-5.\r\n\r\n\r\nBug report info:\r\n\r\n*  I am using the Chrome(50) browser.\r\n*  My computer's OS is OS X 10.11.4.\r\n*  I'm running Metabase on AWS.\r\n*  My database is Postgres.\r\n*  My Metabase version is v0.16.1."},"comment":{"url":"https://api.github.com/repos/metabase/metabase/issues/comments/254625938","html_url":"https://github.com/metabase/metabase/issues/2517#issuecomment-254625938","issue_url":"https://api.github.com/repos/metabase/metabase/issues/2517","id":254625938,"user":{"login":"tlrobinson","id":18193,"avatar_url":"https://avatars.githubusercontent.com/u/18193?v=3","gravatar_id":"","url":"https://api.github.com/users/tlrobinson","html_url":"https://github.com/tlrobinson","followers_url":"https://api.github.com/users/tlrobinson/followers","following_url":"https://api.github.com/users/tlrobinson/following{/other_user}","gists_url":"https://api.github.com/users/tlrobinson/gists{/gist_id}","starred_url":"https://api.github.com/users/tlrobinson/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tlrobinson/subscriptions","organizations_url":"https://api.github.com/users/tlrobinson/orgs","repos_url":"https://api.github.com/users/tlrobinson/repos","events_url":"https://api.github.com/users/tlrobinson/events{/privacy}","received_events_url":"https://api.github.com/users/tlrobinson/received_events","type":"User","site_admin":false},"created_at":"2016-10-18T20:15:14Z","updated_at":"2016-10-18T20:15:14Z","body":"I believe the inconsistency between tooltips and tick labels was resolved in 0.20.0, but whether we're showing the \"correct\" week number is still an open question.\r\n\r\n@whatthewhat We group by weeks starting on Sunday (the US standard) rather than the [ISO week date](https://en.wikipedia.org/wiki/ISO_week_date) standard, which I assume is what you expected?\r\n\r\nFor postgres this is the SQL we generate:\r\n\r\n`(date_trunc('week', (column_name + INTERVAL '1 day')) - INTERVAL '1 day')`\r\n\r\nusing this code on the backend:\r\n\r\n```\r\n    ;; Postgres weeks start on Monday, so shift this date into the proper bucket and then decrement the resulting day\r\n    :week            (hx/- (date-trunc :week (hx/+ expr one-day))\r\n                           one-day)\r\n    :week-of-year    (extract-integer :week (hx/+ expr one-day))\r\n```\r\n\r\nhttps://github.com/metabase/metabase/blob/ec356f7fe3b6e4b67aea36f742f1fe77af005e7b/src/metabase/driver/postgres.clj#L138\r\n\r\nand this code to format it on the frontend:\r\n\r\n```\r\n        case \"week\": // 1st - 2015\r\n            // force 'en' locale for now since our weeks currently always start on Sundays\r\n            m = m.locale(\"en\");\r\n            return formatMajorMinor(m.format(\"wo\"), m.format(\"gggg\"), options);\r\n```\r\n\r\nhttps://github.com/metabase/metabase/blob/ec356f7fe3b6e4b67aea36f742f1fe77af005e7b/frontend/src/metabase/lib/formatting.js#L61-L64\r\n\r\nI believe `wo` and `gggg` follow the US standard, which says the first day of the week is Sunday and the first week of the year includes January 1st (whereas `Wo` and `GGGG` follow the ISO standard, which says the first day of the week is Monday, and the first week of the year includes January 4th)\r\n\r\nFor example this results in `2016-01-07` being put into the `2016-01-03T00:00:00.000Z` bucket, which is formatted as `2nd - 2016` because `2016-01-03` is a Sunday, thus the start of the \"2nd\" week of 2016, according to the US standard, \r\n\r\nSo I believe we at doing something reasonable/correct on both the backend and frontend for `week` groupings.\r\n\r\nHowever, I do think there a bug in the `week-of-year` extract. Postgres' `week` extract follows the ISO week standard, and `ISO weeks start on Mondays and the first week of a year contains January 4 of that year. In other words, the first Thursday of a year is in week 1 of that year.` whereas the US standard week (which I think is what our `moment` formatting code is doing) says that the first week of the year starts on January 1. This isn't an issue for `week` because we're basically using `date_trunc('week', ...)` to round to the week starting on Sunday, but in the case of `week-of-year` we get the actual week number.\r\n\r\nI think we need to do two things:\r\n\r\n1) Compute `week-of-year` correctly (consistent with `week` formatting on the frontend)\r\n2) Maybe give the user control over the 1st day of week and 1st week of year (or maybe just simplified options like ISO week standard and US week standard?)\r\n\r\nHere's a summary of my understanding:\r\n\r\n                    | 1st day of week | 1st week of year           |\r\n| ----------------- |-----------------| ---------------------------|\r\n| US standard       | Sunday          | January 1st                |\r\n| ISO standard      | Monday          | Contains Jan 4 / 1st Thurs |\r\n| MB `week`         | Sunday          | January 1st                |\r\n| MB `week-of-year` | Sunday          | Contains Jan 4?            |\r\n"}}
{"actor":{"display_login":"tlrobinson"}}