Monthly Statistics do not show the right numbers

[JNB-Stravinsky]

Describe the bug

Statistics dashboard does not show the right numbers in Monthly view. The sum of the daily data of a month does not correspond to the total value shown.

Expected behavior

The Statistics monthly view must show the sum of the daily data of each month.

How to reproduce it (as minimally and precisely as possible):

Steps to reproduce the behavior:

1. Go to 'Statistics'
2. Click on 'Period -> Month'
3. Compare the values against 'Period -> day' and sum the data of the days of the month.
4. See error

Environment

• TeslaMate version: 1.20.1
• Type of installation: Docker
• OS TeslaMate is installed on: MacOS 10.15.7
• User OS & Browser: MacOS 10.15.7 - Safari

[DrMichael]

This looks indeed very strange. I checked some of my months, all are correct. Don't know, how to pin that down without having the database at hand.

(I assume, you did not change the display time in the upper right or the timezone...)

[JNB-Stravinsky]

Just noticed the error is related with the Timezone. My current Timezone is Europe/Madrid and the statistics are wrong:

But if I change the Timezone to UTC, it seems to correct the statistics by adding +1 to each month:

[DrMichael]

How did you set the time zone to Europe/Madrid? Switching between Europe/Madrid and Etc/UTC does not change anything for me in respect to drives...

[JNB-Stravinsky]

I didn't set it or I don't remember setting it. It is in Europe/Madrid by default. I think Grafana takes it from the server:

% ls -l /etc/localtime
lrwxr-xr-x 1 root wheel 39 27 sep 10:14 /etc/localtime -> /var/db/timezone/zoneinfo/Europe/Madrid

But teslamate is in UTC format:

% docker exec -it teslamate_teslamate_1 date
Thu Nov 5 09:42:48 UTC 2020

Localtime is UTC+1:

% date
jueves, 5 de noviembre de 2020, 10:44:42 CET

So, I'm seeing two problems:

1. Grafana database is in a different time (UTC) than my local time (UTC+1 now), but this is not really a problem.
2. Monthly Statistics are showing the data of the next month when local time (Europe/Madrid) is not changed to UTC. I have also noticed that changing it to Etc/GMT+1 (also my local time) also shows the right values as well as UTC:

[DrMichael]

Could you please also check date in the grafana and the database container? Which PostgreSQL version are you using?

[JNB-Stravinsky]

Here you have it:

Grafana:

% docker exec -it teslamate_grafana_1 date
Fri Nov 6 18:22:23 UTC 2020

Database:

% docker exec -it teslamate_database_1 date
Fri 06 Nov 2020 06:23:22 PM UTC

My machine date:

% date -u
viernes, 6 de noviembre de 2020, 18:23:50 UTC

PostgreSQL version: 12.4

[DrMichael]

Looks ok, todo bien. I assume, you did not set TZ or PGTZ in your docker-compose.yml or .env there.

What gives docker-compose exec database grep timezone /var/lib/postgresql/data/postgresql.conf?

(Still wondering, where that Europe/Madrid is coming from...)

Anyway, could you import the attached dashboard and have a look at it? Statistics.zip

[leewillis77]

I was about to open a separate report for the Statistics view, but I think my issue is the same as reported here. Here's what I say (two different examples).

Example 1:
Default "Statistics" view opened from Teslamate, period is "Month" and timezone is "Europe/London".

Note how "October 2020" is completely missing, but also data is reported for "August", but the car wasn't tracked in Teslamate until September

Changing the period to "Week" confirms that there is data for October, and that the earliest data is for September

Notice, that the total number of drives tallies across the two views, so the data has obviously gone into the wrong month in month view.

Changing timezone to Etc/UTC does "fix" the data, correctly reporting the same 233 drives across Sep/Oct/Nov, rather than Aug/Sep/Nov

Example 2:
With timezone left at my default ("Europe/London"), the data 'misses' October 2019, and October 2020, and includes two entries for March 2020
https://www.dropbox.com/s/i7vf5py9ro2ujdp/Screenshot%202020-11-12%20at%2009.20.24.png?dl=0

Swapping to UTC correctly shows one entry for each month:

My timezone in Grafana is set to "Default", so assume that Europe/London is being grabbed from my browser / OS (I am in the UK, so the choice of Europe/London is correct).