Timezone issues #939
Comments
|
Morning @hoorna thanks for the question. Let me go through this and see if I can break down each of your points...
The main goal of
UTC time of the result has and will continue to be stored in the database this way so a consistent record can exist for the historic runs and then be calculated to the correct offset based on the user's location. This is generally considered best practice when recording and displaying of dates and times.
PHP's Tested this assumption by dumping Since you're in a different time zone than me would you be up for some testing once I tag the beta version? The changes I've made are working as intended for me and I haven't come across any incorrect running of the scheduler or displaying of datetime stamps yet. |
|
Thanks @alexjustesen you for your detailed answers. I few minutes ago I installed version 0.14.0-beta1 as you asked for. Regarding the Speedtest-tracker container I didn't use the TZ environment variable nor the time volume definition and on the General web page I defined my Timezone Europe/Amsterdam. In the dockercompose file for the MariaDB container the TZ environment variable has the value Europe/Amsterdam. The latter is necessary because I also use this container for other applications.
Let me further give some response on your reply:
I have not much knowledge about PHP nor Carbon but my question is if the routines you mentioned are accurate for times recorded in the past (i.e. times from measuments which are done with 'another' DST flag). The routines will of course return the correct DST time/flag of current moment but will they also do that for times recorded in a period with a different DST flag?
It is of course fine that you make your own choice and I respect that. After all, it is your app. :-) But I wonder if you're not complicating things unnecessarily. If you record the actual local time of measurements in the database, a problem will only arise if a user takes his database to another time zone. That chance seems zero and negligible to me. Of course I would like to help you with further timezone tests. Just let us know what youre needs are. |
That's an interesting nugget because I'm not passing any time zone to the database and I'm starting to think that could be causing the issue. I'm in the office and probably won't circle back around to this until tomorrow but will absolutely be testing this as it differs from my test cases. |
That is indeed a possible cause for the issues. I am of course very curious about your findings. |
|
Databases ALWAYS work with UTC. And even if you move them to other time-zones, data time continues to work properly, exactly because it stores times internally as UTC. It then transforms at the presentation layer timings in local format, depending on the OS configuration. All apps should behave the same way. @alexjustesen: I think we already had a long discussion about this in a related issue, and we're here again at it, or maybe I'm wrong and it was another project/repo, I'm starting to get confused with all these services in the homelab. :) |
I'm not sure exactly what you mean by this (Google Translate is failing me here). I hope that new contributions will continue to be welcomed :-) even though they may have already been discussed. After all, the time zone issues have been going on for a year now and despite all those discussions, they have still not been resolved. |
|
From what I read regarding php/mysql: <?php
date_default_timezone_set('Europe/Dublin');
$myDate = date("Y-m-d H:i:s"); // mysql timestamp: (YYYY-MM-DD HH:MM:SS)
mysql_query("insert into `mytable` set `mytime` = '$myDate'");
?>
What I don't understand is this: the records in the DB are correct. Why do you think you're storing it incorrectly? The problem is when you present them in the UI, not the raw record. |
|
@alexjustesen, did you found some time to test passing an TZ environment vairable to your database container? I am curious. |
Unfortunately not yet, the day job and clients have been keeping me busy the last couple of days. Hopefully tonight I will be able to. |
Not concerned about how it's stored as I'm considering that solid but I am trying to figure out why only a handful of time zones are an issue at this point. We're in this it works fine in my tz circle right now. |
|
If you follow all reports you'll get crazy. :) TZ on the db container doesn't change anything, since records are already created correctly. It's something related to the code or Laravel setting, on the presentation layer, since the data layer seems solid. |
Good luck, but ..... there is no huge rush. Think about your night's sleep. |
Thanks for that! It is pretty annoying that the displayed times are not right and also that the scheduled times does not fit. I am happy with your intention to fix this despite others who are stating that you do not have to follow all reports. That people have probably no problems I suppose: that's easy. Remember that I am gladly willing to help where I can. |
I was referring to finding the bug, not following the issues, try to understand the context instead of misinterpreting. TZ on the database container has nothing to do with this issue. |
|
|
|
Thanks!! I will look after it as soon as I can and I will report back. |
|
Here is the output from /debug/timezone with version 0.14.0-beta3. It seems to me that all values are correct for my timezone.
|
|
The debug page looks ok: But the issue is there: |
I'm going to pull the |
That seems like a good addition to me. If I may make a suggestion: also add the Speedtest schedule from the General web page. You then also have information about when speed tests should have started according to the schedule. |
|
|
|
This is the result of the debug page with v0.14.0-beta4. I think all times seems correct, exept that the result time differs from the time I scheduled the speedtest. This scheduled time is 01:15. There is a uncertainty though: I cannot check/see anywhere on which time the speedtest really ran (01:15 or 02:15).
|
|
Here is beta4. Last result is the same I show you via screenshots, as expected. +1 ahead.
|
|
BTW: I checked inside the container, and date command shows it's UTC based. > dcx -it speedtest-tracker bash
root@851f45b24c39:/var/www/html# date
Thu Nov 30 12:42:57 UTC 2023In order to correct my issue, I reintroduce /etc/localtime and here are the results of debug page and date shell command:
root@9120a23c4ddc:/var/www/html# date
Thu Nov 30 13:46:35 CET 2023
root@9120a23c4ddc:/var/www/html#As you can see above, when it's working correctly for me, Local Time and UTC Time are the same, and that's wrong. |
|
The date command inside the container (without TZ nor timezone vollume variable) return "UTC" (so 1 hour back). The date command with the TZ environment variable set (Europe/Amsterdam) gives "CET", which is correct). The output from the /debug/timezone web page is in for me in this case exactly the same as with no TZ nor timezone volume environment variable: UTC time and Local time differs one hour. This is another outcome than @alexdelprete reports above (UTC time and local time the same), but he was using the timezone volume environment variable. Furthermore: a question: Isn't it strange that with the TZ environment set (to Europe/Amsterdam) the /debug/timezone output for "PHP time zone" and "App time zone" stays at UTC? |
So... When passing The time zone of Ubuntu (in this case) is out of scope of this issue and doesn't (or at least shouldn't) effect the date time of PHP. PHP should continue to operate under the UTC time but what I'm trying to accomplish here is the schedulers time and the display time being correct by setting a time zone in the UI (Settings -> General). |
|
That is a totally wrong assumption, and since
Totally wrong. An app should never rely on the configuration of the database, since the database could be used for several apps (in a cloud scenario for example). So setting the correct TZ for the database container is not a wrong configuration, but the correct one. I use MariaDB as my central instance for all homelab apps/services, and only Speedtest-Tracker has issues with timestamps. The problem is that you're not setting the correct timezone for the connection, or you're forcing UTC for the connection, and that's wrong. You should set the connection based on user's selected timezone.
Hope this clears the problem once and for all. :) |
|
Checked the ST code, and in config/database.php there's no time zone set for the connection. So when ST writes This is what I found to set the time zone at connection level: 'options' => array(
\PDO::MYSQL_ATTR_INIT_COMMAND => 'SET time_zone = $offset'
)Others report this also to be working: 'mysql' => [
'driver' => 'mysql',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
'strict' => false,
'timezone' => '$offset'
],And I also read this: try {
$conn = new PDO( "mysql:host=$hostname;dbname=$dbname", $username, $password );
// Set DB Timezone using the Offset
$conn->exec( "SET time_zone='$offset';" );
echo "Connected successfully";
}
catch( PDOException $e ) {
echo "Failed to connect: " . $e->getMessage();
}So 3 different ways, it needs to be tested I guess. To calculate the offset: // Define Timezone globally
define( 'TIMEZONE', 'America/Los_Angeles' );
// Set PHP Timezone
date_default_timezone_set( TIMEZONE );
// Obtain the Offset
$time = new DateTime();
$offset = $time->format( 'P' );Unfortunately Some articles about this topic: |
@alexjustesen, I agree with @alexdelprete that the assumption from Speedtest Test tracker is incorrect. The data from my server and running containers prove this. See the data at the bottom of this message. In my MariaDB database, the "created_at" column displays the local recorded time and not the UTC time. I think @alex clearly explains why that is the case. I also agree with @alexdelprete that for both containers the TZ environment variable has to be set. Just like him I need the TZ variable definition for the database container because of other running applications/containers. Furthermore, it is a normal procedure for containers to define the TZ environment variable. Finally, I notice that the "data" column in the database contains the recorded UTC time. If you really need the recorded UTC time for displaying the time on the Results tab, why not using the time from the "data" column? Possible solutionsTo keep it simple I think there are two possible solutions for the timezone issue on the Speedtest results tab.
@alexjustesen, I really hope this helps for solving the timezone issue! I fully understand your frustration with this issue and therefore remain available for assistance. My data (Release v0.14.0-beta5)Localtime
With TZ=Europe/Amsterdam set on both Speedtest and MariaDB container en no timezone volume defintion set
|
|
Alright I spent a couple of days thinking about this and the path forward, so here's the final decision so I can move on... Final Decision
This vs. That
I agree this is a viable pattern and a requirement of other applications and containers. However for this application the standard will be the database should run in UTC and not your local time zone. Broadly speaking we don't change the time zone of the database in any of the other applications, websites and tools I've developed so I'm not deviating from that standard here either. One Last Comment...I'll be closing this issue after this comment and sticking a nail in it's coffin so I can move on to other features. I'll be tagging |
In this post I try to think about the timezone issues of Speedtest-tracker. My suggestions are intended to help.
In the v0.14.0 version notes I read that the time zones are handled in a different way. If I understand correctly, from version 0.14.0 onwards, the UTC time of the measurement moment is stored in the database and not (anymore) the local time (as in previous versions). On the General web page the user sets the local time zone so that the correct local time of the measurement moment should be displayed on the Results web page (and not the UTC time from the database).
I wonder whether this is a wise choice, especially for regions where daylight saving time (DST) is applied. I'll try to explain why.
Speedtest-tracker probably only records the UTC time of a measurement moment in the database and not the DST of that moment (let's call this the time of measurement on day 0). If the DST zone does not change, the correct local time of the measurement moment of day 0 will be displayed on day 1 on the Results web page. This display is determined by the region entered on the General web page. Nothing to worry about.
If the DST zone does change the following night (at the start of day 2), Speedtest-tracker will from that moment on display all times on the Results web page in the new DST times. Speedtest-tracker follows the region time, which is entered on the General web page. The effect is that the Results web page now displays an incorrect time of the measurement on day 0 (namely in the 'new' DST time zone instead of the 'old').
If my reasoning is correct, then the only correct way is to record the local (DST) time of the region in the database for the measurement moments (and not the UTC time). This time should be determined by the TZ environment variable. The region setting on the General web page should be omitted in this approach. The Results page displays the correct time of the measurement as recorded in the database.
Finally, I would like to make a comment about DST and Speedtest-tracker. I don't know if this is correct, but I have the impression that some of the time zone issues reported so far are related to the DST zone users are in. My suspicion is that Speedtest-tracker has not taken enough account of this so far. This suspicion seems to be confirmed because in version 0.14.0 the time of the measurement moments on my Results web page is still displayed incorrectly (with a time difference of 1 hour in accordance with the DST zone in which I live: Europe/Amsterdam).
The text was updated successfully, but these errors were encountered: