Server calendar in Online Activity Overview Network page shows incorrect data

[ipozow]

Describe the issue

The data shown in Server calendar it's delayed a day. For example, in Sunday I had 418 unique players and 112 new players and it says that Saturday I had 112 new players and 418 unique but that happened Sunday, not Saturday. Today is Monday and it says that what is happening today happened Sunday (as it shows the image)

Another image for a better understanding:

Server information

I have multiple server version, one is 1.14.4 and the other 1.13.2 paper 637 and in both is the same issue. I'm running a network too, Waterfall.

Server Information

Plan Version: 5.0 build 292 Server: BungeeCord git:Waterfall-Bootstrap:1.14-SNAPSHOT:cccb337:296 (git:Waterfall-Bootstrap:1.14-SNAPSHOT:cccb337:296) Database: MySQL

Operating SubSystem: Linux (amd64) version 4.9.33-mod-std-ipv6-64 Java Version: 1.8.0_212, Oracle Corporation Java VM Version: OpenJDK 64-Bit Server VM version 25.212-b03, Oracle Corporation

Exceptions & Other Logs

Additional information

---

by AuroraLS3

This appears to happen with MySQL and has been fixed for SQLite

TODO

• Reproduce the issue with a unit test running MySQL
• Fix the issue (easy right 😅)

[AuroraLS3]

What timezone is your server using?

[AuroraLS3]

Do the /server pages have the same issue?

[ipozow]

Actually these were the /server pages And timezone is GMT-5

[AuroraLS3]

Are the other dates correct (Today Yesterday etc?) - Could you include a screenshot of the full calendar and 25 players from Player list tab sorted by most recent register date (I only need the register dates, no need for other columns)

[ipozow]

And no, the other dates aren't correct. They are delayed one day too

[AuroraLS3]

It looks like the issue is limited to the calendar and the graph and other times are correct, while calendar is delayed by one day.

There is likely some logic mistake that is related to negative timezone offset that causes the days to be grouped on the previous day, I'll take a look at it when I have the time

[AuroraLS3]

I found out the cause of the issue and why it doesn't occur with GMT +X timezones

Where	Applied Offset	Description
In Database	+0	Stored as UTC
In query	-5	to group,, output as UTC 0 AM milliseconds
To Graph JSON	+0	output as UTC 0 AM milliseconds
To Calendar JSON	-5 (-10 total)	Applies timezone offset (Second time) while formatting milliseconds to ISO-8601 (Calendar wants this), causing the date to roll to the wrong day

In GMT +12 formatting to ISO-8601 (yyyy-MM-dd) does not roll to the next day, but in -1 it does roll to the previous day

[AuroraLS3]

Here is a build with the fix done above: Plan-5.0 build 293.jar.zip

I want to do some other stuff before full release.

[ipozow]

This is still present in 335 :(

[AuroraLS3]

Could you send new screenshots

[ipozow]

Yes

[AuroraLS3]

I was not able to reproduce the issue because my computer is using GMT+3 as timezone, and the issue was likely caused by FullCalendar using 'local' instead of 'UTC' as the timezone. Plan was giving the dates for this calendar as UTC at midnight so a negative timezone would move them back.

Should be fixed now, but if it still happens in next release, please comment again.

[AuroraLS3]

Still happening it seems.

[AnttiMK]

Cause of the issue found, ref: https://discord.com/channels/364107873267089409/635887328770129922/840732429714325544

[AuroraLS3]

Caught this in a test by accident by running test at 21:01GMT+3

MySQLTest#sqlParsingSanityCheck() SELECT DATE(FROM_UNIXTIME(1625259676)) as date

expected: <2021-07-02> but was: <2021-07-03> Expected :2021-07-02 Actual :2021-07-03

[AnttiMK]

related?

SQLiteTest > sqlDateParsingSanitySQLDoesNotApplyTimezone() FAILED
    org.gradle.internal.exceptions.DefaultMultiCauseException: Multiple Failures (3 failures)
    	org.opentest4j.AssertionFailedError: Expected <2021-07-07> but was: <2021-07-06> for query <SELECT strftime('%Y-%m-%d',datetime(1625608531, 'unixepoch')) as date> ==> expected: <2021-07-07> but was: <2021-07-06>
    	org.opentest4j.AssertionFailedError: Expected <2021-07-07> but was: <2021-07-06> for query <SELECT strftime('%Y-%m-%d',datetime(1625612131, 'unixepoch')) as date> ==> expected: <2021-07-07> but was: <2021-07-06>
    	org.opentest4j.AssertionFailedError: Expected <2021-07-07> but was: <2021-07-06> for query <SELECT strftime('%Y-%m-%d',datetime(1625615731, 'unixepoch')) as date> ==> expected: <2021-07-07> but was: <2021-07-06>

[AuroraLS3]

Host machine timezone is used even when the session timezone set is attempted

hikariConfig.addDataSourceProperty("connectionInitSql", "set time_zone = '+00:00'");

Reproduced the issue again similar to previous comment

MySQLTest#sqlParsingSanityCheck() Expected <2022-05-26> but was: <2022-05-27> for query <SELECT DATE(FROM_UNIXTIME(1653599995)) as date> ==> expected: <2022-05-26> but was: <2022-05-27> Expected :2022-05-26 Actual :2022-05-27

[AuroraLS3]

Reverting commit 396b51873cbcc2103039bb986a5ab53c44c8e703 (Related issue #1482) fixes tests. It appears the timezone is not set by the hikaricp property, it might be ignored entirely.

Hopefully that resolves this issue.

[AuroraLS3]

Still occurs when using SQLite (MySQL should be fixed)

[Subnet-Masked]

@AuroraLS3 I just poked in to check on the status of this and noticed your comment that MySQL should be fixed... I installed build 2820 of 5.6 and the calendar is off by a day still. My timezone is GMT-4 and I am using MariaDB 15.1