Heatmap: Actions are shown on the previous day #2920

@Jamozed commented on GitHub (Mar 30, 2019):

NZST timezone (UTC +12:00/13:00) also does this, except only before midday.

@Jamozed commented on GitHub (Mar 30, 2019): NZST timezone (UTC +12:00/13:00) also does this, except only before midday.

GiteaMirror commented

@kolaente commented on GitHub (Apr 9, 2019):

This probably has to do with the fact the server runs in a different timezone (most likely utc) than your browser.

@kolaente commented on GitHub (Apr 9, 2019): This probably has to do with the fact the server runs in a different timezone (most likely utc) than your browser.

GiteaMirror commented

@Jamozed commented on GitHub (Apr 10, 2019):

This probably has to do with the fact the server runs in a different timezone (most likely utc) than your browser.

This was the first thing I checked in my case, my server and browser are absolutely running in the same timezone.

@Jamozed commented on GitHub (Apr 10, 2019): > This probably has to do with the fact the server runs in a different timezone (most likely utc) than your browser. This was the first thing I checked in my case, my server and browser are absolutely running in the same timezone.

GiteaMirror commented

@siddweiker commented on GitHub (Apr 10, 2019):

I think its because the time is striped from the timestamp.
c42bde719e/models/user_heatmap.go (L25)

@siddweiker commented on GitHub (Apr 10, 2019): I think its because the time is striped from the timestamp. https://github.com/go-gitea/gitea/blob/c42bde719ecf2dd8757453bed18e082d14c1d3fa/models/user_heatmap.go#L25

GiteaMirror commented

@stale[bot] commented on GitHub (Jun 9, 2019):

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

@stale[bot] commented on GitHub (Jun 9, 2019): This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs during the next 2 weeks. Thank you for your contributions.

GiteaMirror commented

@Brayyy commented on GitHub (Dec 29, 2020):

This issue is still present on 1.14.0+dev-394-g069acf6a2.

It is caused by a disparity in the timezone of the DB (MySQL in my case), and the local browser. My company mainly works out of the EDT timezone, but I am in PST (three hours behind). When I push code, I always see it appear on the prior day, however, if I adjust my local system time to be EDT, the blocks align correctly.

As noted, the issue is rooted in the DB query roundingcreated_unix down to the local day, then in the browser, JS applies the TZ offset, and then rounds this value down a second time, which is now the prior day.

The best solution would likely be to read the timezone from the user browser and use that when globbing the created_unix hours into a single day.

@Brayyy commented on GitHub (Dec 29, 2020): This issue is still present on 1.14.0+dev-394-g069acf6a2. It is caused by a disparity in the timezone of the DB (MySQL in my case), and the local browser. My company mainly works out of the EDT timezone, but I am in PST (three hours behind). When I push code, I always see it appear on the prior day, however, if I adjust my local system time to be EDT, the blocks align correctly. As noted, the issue is rooted in the DB query rounding`created_unix` down to the local day, then in the browser, JS applies the TZ offset, and then rounds this value down a second time, which is now the prior day. The best solution would likely be to read the timezone from the user browser and use that when globbing the `created_unix` hours into a single day.

GiteaMirror commented

@Colin5887 commented on GitHub (Jan 9, 2021):

@Brayyy

I think there are two problems: incorrect heatmap and incorrect commission time.

The commission time (the text popup when hovering on the "19 hours ago") can be changed by putting a time section in the setting. Everyone who accesses that gitea server will use the same time zone.

However, the heatmap uses UTC+0 time zone and there is currently no way to change that. I live in AEDT (UTC+10) and every commission before 10:00 will be marked as yesterday's work.

@Colin5887 commented on GitHub (Jan 9, 2021): @Brayyy I think there are two problems: incorrect heatmap and incorrect commission time. The commission time (the text popup when hovering on the "19 hours ago") can be changed by putting a `time` section in the setting. Everyone who accesses that gitea server will use the same time zone. However, the heatmap uses UTC+0 time zone and there is currently no way to change that. I live in AEDT (UTC+10) and every commission before 10:00 will be marked as yesterday's work.

GiteaMirror commented

@Colin5887 commented on GitHub (Jan 9, 2021):

@siddweiker

The heatmap uses UTC+0 time zone and cannot be changed by any settings (so far). The only way you can make a commission today is to push your work after 19:00 (since you live in UTC-5).

@Colin5887 commented on GitHub (Jan 9, 2021): @siddweiker The heatmap uses UTC+0 time zone and cannot be changed by any settings (so far). The only way you can make a commission today is to push your work after 19:00 (since you live in UTC-5).

GiteaMirror commented

@kins-dev commented on GitHub (Feb 25, 2021):

@siddweiker

The heatmap uses UTC+0 time zone and cannot be changed by any settings (so far). The only way you can make a commission today is to push your work after 19:00 (since you live in UTC-5).

I think you have that backwards.
I'm using PST (UTC -8). If I push at 1:00 AM, that is 9 AM UTC+0 of the same day.
https://www.google.com/search?q=1%3A00+AM+PST+to+UTC

If I push at 5 PM (17:00) then that should be shown as 1:00 AM on the next day if the heat map is UTC based.
https://www.google.com/search?q=17%3A00+PST+to+UTC

It should never show up on the previous day if the user has a negative offset to UTC. The heat map is just wrong.

Edit:
It appears this is a result of the timestamp in heatmap.json, being converted from UTC to the local time zone. For example If I have:

[{"timestamp":1614211200,"contributions":2}]

1614211200 represents Thursday, February 25, 2021 12:00:00 AM UTC
https://www.epochconverter.com/
If my browser then takes that as UTC and converts it to local time, that's Wednesday, February 24, 2021 4:00:00 PM UTC-08:00

So the timestamp being generated for grouping is causing the issue.
One possible way to fix this would be to go to:
https://github.com/go-gitea/gitea/blob/4172b1955fabe79e5918145f4ad1b00911390de0/web_src/js/features/heatmap.js#L11
use this instead:

      return {date: new Date((timestamp + ((new Date()).getTimezoneOffset() * 60)) * 1000), count: contributions};

I haven't actually tested out this change, but based on the JSON data and using my javascript console this should work.

@kins-dev commented on GitHub (Feb 25, 2021): > @siddweiker > > The heatmap uses UTC+0 time zone and cannot be changed by any settings (so far). The only way you can make a commission today is to push your work after 19:00 (since you live in UTC-5). I think you have that backwards. I'm using PST (UTC -8). If I push at 1:00 AM, that is 9 AM UTC+0 of the same day. <https://www.google.com/search?q=1%3A00+AM+PST+to+UTC> If I push at 5 PM (17:00) then that should be shown as 1:00 AM on the next day if the heat map is UTC based. <https://www.google.com/search?q=17%3A00+PST+to+UTC> It should never show up on the previous day if the user has a negative offset to UTC. The heat map is just wrong. Edit: It appears this is a result of the timestamp in heatmap.json, being converted from UTC to the local time zone. For example If I have: ```json [{"timestamp":1614211200,"contributions":2}] ``` 1614211200 represents Thursday, February 25, 2021 12:00:00 AM UTC <https://www.epochconverter.com/> If my browser then takes that as UTC and converts it to local time, that's Wednesday, February 24, 2021 4:00:00 PM UTC-08:00 So the timestamp being generated for grouping is causing the issue. One possible way to fix this would be to go to: <https://github.com/go-gitea/gitea/blob/4172b1955fabe79e5918145f4ad1b00911390de0/web_src/js/features/heatmap.js#L11> use this instead: ```js return {date: new Date((timestamp + ((new Date()).getTimezoneOffset() * 60)) * 1000), count: contributions}; ``` I haven't actually tested out this change, but based on the JSON data and using my javascript console this should work.

GiteaMirror commented

@siddweiker commented on GitHub (Feb 25, 2021):

@kins-dev That will not work;
Given a user who is in the EST timezone:

Database Value
- Timestamp: 1612141200
- GMT: 2021-02-01 01:00:00+00:00
- EST: 2021-01-31 20:00:00-05:00
Selected Value
- Timestamp: 1612137600
- GMT: 2021-02-01 00:00:00+00:00
- Javascript EST: 2021-01-31 19:00:00-05:00
- Your JS EST: 2021-02-01 00:00:00-05:00
Expected Value
- EST: 2021-01-31 00:00:00-05:00

The opposite happens for 1612306800 (2021-02-02 18:00:00-05:00), the JS will be the previous day but your JS will be correct.

The server is not aware of the client's timezone, so when it strips out the time, you will get shifted results on the client side.
The SQL should not group at all, and it should be done in heatmap.js.

A better solution would be to group to the hour in SQL, then have the JS convert it to local date (strip the time) and summarize it once more. This will send one timestamp per hour instead of every commit's timestamp.

@siddweiker commented on GitHub (Feb 25, 2021): @kins-dev That will not work; Given a user who is in the EST timezone: - Database Value - Timestamp: `1612141200` - GMT: `2021-02-01 01:00:00+00:00` - EST: `2021-01-31 20:00:00-05:00` - Selected Value - Timestamp: `1612137600` - GMT: `2021-02-01 00:00:00+00:00` - Javascript EST: `2021-01-31 19:00:00-05:00` - Your JS EST: `2021-02-01 00:00:00-05:00` - Expected Value - EST: `2021-01-31 00:00:00-05:00` The opposite happens for `1612306800` (`2021-02-02 18:00:00-05:00`), the JS will be the previous day but your JS will be correct. The server is not aware of the client's timezone, so when it strips out the **time**, you will get shifted results on the client side. The SQL should not group at all, and it should be done in heatmap.js. A better solution would be to group to the hour in SQL, then have the JS convert it to local date (strip the time) and summarize it once more. This will send one timestamp per hour instead of every commit's timestamp.

GiteaMirror commented

https://jsfiddle.net/3v578s1r/1/

@kins-dev commented on GitHub (Feb 26, 2021):

@kins-dev That will not work;
Given a user who is in the EST timezone:
* Database Value
  
  * Timestamp: `1612141200`
  * GMT: `2021-02-01 01:00:00+00:00`
  * EST: `2021-01-31 20:00:00-05:00`

* Selected Value
  
  * Timestamp: `1612137600`
  * GMT: `2021-02-01 00:00:00+00:00`
  * Javascript EST: `2021-01-31 19:00:00-05:00`
  * Your JS EST: `2021-02-01 00:00:00-05:00`

* Expected Value
  
  * EST: `2021-01-31 00:00:00-05:00`
The opposite happens for 1612306800 (2021-02-02 18:00:00-05:00), the JS will be the previous day but your JS will be correct.

The server is not aware of the client's timezone, so when it strips out the time, you will get shifted results on the client side.
The SQL should not group at all, and it should be done in heatmap.js.

A better solution would be to group to the hour in SQL, then have the JS convert it to local date (strip the time) and summarize it once more. This will send one timestamp per hour instead of every commit's timestamp.

Really? Did you try it? Did you understand the issue? (The calendar.vue is running in the context of local time, for showing days but the server is providing the date via epoch which evaluates to the previous day when your timezone is behind UTC. You can think of it as taking the time specified and doing dropping the time information but keeping the date information. Using epoch time implies that time is in the UTC zone, so that information is not being stripped when it goes to your browser.)

So, I'm PST, but this will work for EST or any other timezone.
I made a brand new server and commits today, the result was as I stated before:

[{"timestamp":1614211200,"contributions":2}]

That's not February 25th where my browser or server is running. It is February 25th UTC time, in fact the very first moment of it. February 25th started at 1614240000 for the PST time zone.

So, doing the math 1614240000 - 1614211200 = 28800 seconds.
28800 seconds is 480 minutes which is 8 hours. Given that PST is GMT -8, that makes sense. (We're 8 hours behind GMT.)
Well what is:

(new Date()).getTimezoneOffset() * 60 + 1614211200

Does that match 1614240000?

I think it does, and in fact after putting my code in place, I get this now:

This is not perfect, far from it, but it is at least defensible. I'm using math to make the calendar.vue work based on UTC time so it matches the server in terms of what constitutes a single day. Why do you believe that won't work?

Edit:
That picture is a little hard to read, but you can see it says my account joined today. Here's the heatmap without the popup.

Before my change, those contributions all showed up the previous day in the heatmap

Edit 2: some wording.

Edit 3: Just realized it is just after midnight UTC, so I made a few more commits:

You can see the commits that should be attributed to 2/26/2021 are not shown here.

The heatmap data is now:

[{"timestamp":1614211200,"contributions":12},{"timestamp":1614297600,"contributions":3}]

12 + 3 = 15 contributions and it is obvious the 3 are connected to the next day.

heatmap.vue and calendar.vue will not show the next day until it hits the 26th local time, so for now that data is not visible. Like I said not perfect, but better than putting items on the wrong day.

@kins-dev commented on GitHub (Feb 26, 2021): > @kins-dev That will not work; > Given a user who is in the EST timezone: > > * Database Value > > * Timestamp: `1612141200` > * GMT: `2021-02-01 01:00:00+00:00` > * EST: `2021-01-31 20:00:00-05:00` > > * Selected Value > > * Timestamp: `1612137600` > * GMT: `2021-02-01 00:00:00+00:00` > * Javascript EST: `2021-01-31 19:00:00-05:00` > * Your JS EST: `2021-02-01 00:00:00-05:00` > > * Expected Value > > * EST: `2021-01-31 00:00:00-05:00` > > > The opposite happens for `1612306800` (`2021-02-02 18:00:00-05:00`), the JS will be the previous day but your JS will be correct. > > The server is not aware of the client's timezone, so when it strips out the **time**, you will get shifted results on the client side. > The SQL should not group at all, and it should be done in heatmap.js. > > A better solution would be to group to the hour in SQL, then have the JS convert it to local date (strip the time) and summarize it once more. This will send one timestamp per hour instead of every commit's timestamp. Really? Did you try it? Did you understand the issue? (The calendar.vue is running in the context of local time, for showing days but the server is providing the date via epoch which evaluates to the previous day when your timezone is behind UTC. You can think of it as taking the time specified and doing dropping the time information but keeping the date information. Using epoch time implies that time is in the UTC zone, so that information is not being stripped when it goes to your browser.) So, I'm PST, but this will work for EST or any other timezone. I made a brand new server and commits today, the result was as I stated before: ```json [{"timestamp":1614211200,"contributions":2}] ``` That's not February 25th where my browser or server is running. It is February 25th UTC time, in fact the very first moment of it. February 25th started at 1614240000 for the PST time zone. So, doing the math 1614240000 - 1614211200 = 28800 seconds. 28800 seconds is 480 minutes which is 8 hours. Given that PST is GMT -8, that makes sense. (We're 8 hours behind GMT.) Well what is: ```js (new Date()).getTimezoneOffset() * 60 + 1614211200 ``` <https://jsfiddle.net/3v578s1r/1/> Does that match 1614240000? I think it does, and in fact after putting my code in place, I get this now: ![Screenshot from 2021-02-25 15-56-21](https://user-images.githubusercontent.com/48720687/109235639-ec5e7800-7782-11eb-8921-a9c840a18e68.png) This is not perfect, far from it, but it is at least defensible. I'm using math to make the calendar.vue work based on UTC time so it matches the server in terms of what constitutes a single day. Why do you believe that won't work? Edit: That picture is a little hard to read, but you can see it says my account joined today. Here's the heatmap without the popup. ![Screenshot from 2021-02-25 16-10-32](https://user-images.githubusercontent.com/48720687/109236327-41e75480-7784-11eb-88f7-fb05cd968433.png) Before my change, those contributions all showed up the previous day in the heatmap Edit 2: some wording. Edit 3: Just realized it is just after midnight UTC, so I made a few more commits: ![Screenshot from 2021-02-25 16-28-10](https://user-images.githubusercontent.com/48720687/109237739-05692800-7787-11eb-92a5-827b41a156f9.png) You can see the commits that should be attributed to 2/26/2021 are not shown here. The heatmap data is now: ```json [{"timestamp":1614211200,"contributions":12},{"timestamp":1614297600,"contributions":3}] ``` 12 + 3 = 15 contributions and it is obvious the 3 are connected to the next day. heatmap.vue and calendar.vue will not show the next day until it hits the 26th local time, so for now that data is not visible. Like I said not perfect, but better than putting items on the wrong day.

GiteaMirror commented

@kins-dev commented on GitHub (Feb 27, 2021):

So I believe the heat map end date can be fixed by:
50208e903a/web_src/js/components/ActivityHeatmap.vue (L38)
to

    endDate: new Date(Date.now() + (new Date()).getTimezoneOffset() * 60000),

And this should keep the heatmap in UTC.

I won't be able to test it for another day.

Of course this all could have been avoided if
c42bde719e/models/user_heatmap.go (L19)
had kept the date string as a string instead of converting it seconds then milliseconds. (As per the documentation here: https://github.com/julienr114/vue-calendar-heatmap) We'd still need to play games with the end date.

That all said there's a better solution.
I want to make sure that someone like @lafriks, @kolaente, or @jolheiser see's this part: since my suggestion probably needs to be evaluated as part of the whole design.

if the function in user_heatmap.go was simplified to: (Remember to remove the unused reference)

func getUserHeatmapData(user *User, team *Team, doer *User) ([]*UserHeatmapData, error) {
        hdata := make([]*UserHeatmapData, 0)

        if !activityReadable(user, doer) {
                return hdata, nil
        }

        cond, err := activityQueryCondition(GetFeedsOptions{
                RequestedUser:  user,
                RequestedTeam:  team,
                Actor:          doer,
                IncludePrivate: true, // don't filter by private, as we already filter by repo access
                IncludeDeleted: true,
                // * Heatmaps for individual users only include actions that the user themself did.
                // * For organizations actions by all users that were made in owned
                //   repositories are counted.
                OnlyPerformedBy: !user.IsOrganization(),
        })
        if err != nil {
                return nil, err
        }

        // Only worry about actions in the last 53 weeks to give a little margin for error
        return hdata, x.
                Select("created_unix AS timestamp, count(user_id) as contributions").
                Table("action").
                Where(cond).
                And("created_unix > ?", (timeutil.TimeStampNow() - (53*7*60*60))).
                GroupBy("created_unix").
                OrderBy("timestamp").
                Find(&hdata)
}

So that everything is just grouped by their unix timestamp.

Then in heatmap.js if you did something like:

export default async function initHeatmap() {
  const el = document.getElementById('user-heatmap');
  if (!el) return;

  try {
    const json_data = JSON.parse(el.dataset.heatmapData).map(({contributions, timestamp}) => {
      return {date: new Date(timestamp * 1000).toDateString(), count: contributions};
    });
    let tmp={};
    json_data.forEach(e => {
        let val = tmp[e.date]||0;
        val += e.count;
        tmp[e.date]=val;
    });
    let heatmap_data = [];
    for(let dateVal in tmp) {
        let countVal = tmp[dateVal];
        heatmap_data.push({date: new Date(dateVal), count: countVal});
    }
    const values = heatmap_data.map(a => ({...a}));

    const View = Vue.extend({
      render: (createElement) => createElement(ActivityHeatmap, {props: {values}}),
    });

    new View().$mount(el);
  } catch (err) {
    console.error(err);
    el.textContent = 'Heatmap failed to load';
  }
}

Then the heatmap would just work in the web browser's time zone and this entire issue would just go away.

Also it reduces the computational load on the server, by making clients sum the number of actions per day, The draw back is, it does increase the amount of data being sent between the client and the server. This may be a problem with a lot of commits, so the person fixing this might want to group by minute, or by a 10 minute interval, but I'll leave that up to them. (The original reason for grouping was not in a comment in the code, so I don't know if there's something I'm missing.)

@kins-dev commented on GitHub (Feb 27, 2021): So I believe the heat map end date can be fixed by: https://github.com/go-gitea/gitea/blob/50208e903ae9aa931db3d93bde7032e766687cae/web_src/js/components/ActivityHeatmap.vue#L38 to ```js endDate: new Date(Date.now() + (new Date()).getTimezoneOffset() * 60000), ``` And this should keep the heatmap in UTC. I won't be able to test it for another day. Of course this all could have been avoided if https://github.com/go-gitea/gitea/blob/c42bde719ecf2dd8757453bed18e082d14c1d3fa/models/user_heatmap.go#L19 had kept the date string as a string instead of converting it seconds then milliseconds. (As per the documentation here: https://github.com/julienr114/vue-calendar-heatmap) We'd still need to play games with the end date. That all said there's a better solution. I want to make sure that someone like @lafriks, @kolaente, or @jolheiser see's this part: since my suggestion probably needs to be evaluated as part of the whole design. if the function in user_heatmap.go was simplified to: (Remember to remove the unused reference) ```go func getUserHeatmapData(user *User, team *Team, doer *User) ([]*UserHeatmapData, error) { hdata := make([]*UserHeatmapData, 0) if !activityReadable(user, doer) { return hdata, nil } cond, err := activityQueryCondition(GetFeedsOptions{ RequestedUser: user, RequestedTeam: team, Actor: doer, IncludePrivate: true, // don't filter by private, as we already filter by repo access IncludeDeleted: true, // * Heatmaps for individual users only include actions that the user themself did. // * For organizations actions by all users that were made in owned // repositories are counted. OnlyPerformedBy: !user.IsOrganization(), }) if err != nil { return nil, err } // Only worry about actions in the last 53 weeks to give a little margin for error return hdata, x. Select("created_unix AS timestamp, count(user_id) as contributions"). Table("action"). Where(cond). And("created_unix > ?", (timeutil.TimeStampNow() - (53*7*60*60))). GroupBy("created_unix"). OrderBy("timestamp"). Find(&hdata) } ``` So that everything is just grouped by their unix timestamp. Then in heatmap.js if you did something like: ```js export default async function initHeatmap() { const el = document.getElementById('user-heatmap'); if (!el) return; try { const json_data = JSON.parse(el.dataset.heatmapData).map(({contributions, timestamp}) => { return {date: new Date(timestamp * 1000).toDateString(), count: contributions}; }); let tmp={}; json_data.forEach(e => { let val = tmp[e.date]||0; val += e.count; tmp[e.date]=val; }); let heatmap_data = []; for(let dateVal in tmp) { let countVal = tmp[dateVal]; heatmap_data.push({date: new Date(dateVal), count: countVal}); } const values = heatmap_data.map(a => ({...a})); const View = Vue.extend({ render: (createElement) => createElement(ActivityHeatmap, {props: {values}}), }); new View().$mount(el); } catch (err) { console.error(err); el.textContent = 'Heatmap failed to load'; } } ``` Then the heatmap would just work in the web browser's time zone and this entire issue would just go away. Also it reduces the computational load on the server, by making clients sum the number of actions per day, The draw back is, it does increase the amount of data being sent between the client and the server. This may be a problem with a lot of commits, so the person fixing this might want to group by minute, or by a 10 minute interval, but I'll leave that up to them. (The original reason for grouping was not in a comment in the code, so I don't know if there's something I'm missing.)

GiteaMirror commented

@siddweiker commented on GitHub (Feb 27, 2021):

I'm in agreement. (This is what I proposed in my previous comment...)

There are 30min and 45min timezones (India is UTC +5:30). So grouping by date and 15 min intervals will give you the most accurate times with the least amount of data sent.

@siddweiker commented on GitHub (Feb 27, 2021): I'm in agreement. (This is what I proposed in my previous comment...) There are 30min and 45min timezones (India is UTC +5:30). So grouping by date and 15 min intervals will give you the most accurate times with the least amount of data sent.

GiteaMirror commented

@Colin5887 commented on GitHub (Feb 27, 2021):

I'm tired of making no contributions every Friday and work overtime every Sunday. Hope this issue can be fixed soon.

BTW, Australian Central Daylight Time (ADST) is a +10.5 time. 15 minutes' interval is a great suggestion.

@Colin5887 commented on GitHub (Feb 27, 2021): I'm tired of making no contributions every Friday and work overtime every Sunday. Hope this issue can be fixed soon. BTW, Australian Central Daylight Time (ADST) is a +10.5 time. 15 minutes' interval is a great suggestion.

GiteaMirror commented