Result times are still being reported/recorded in UTC even after v0.1.0-alpha.10

[JourneyOver]

Even after #71 and updating to the v0.1.0-alpha.10 version, the result times and the database file (in both data / created_at) are still being reported/recorded in UTC instead of the timezone set in the settings panel.

edit: I did try to change https://github.com/alexjustesen/speedtest-tracker/blob/main/app/Filament/Resources/ResultResource.php#L44 to be my timezone as well but sadly that didn't seem to make a difference.

edit: also just a note I thought I'd add in, but the favicon stuff doesn't seem to work either, it just returns a 404.

[spcqike]

I thought UTC as storage time is the way to go. One just uses timezone for UI/frontend.

If you change your timezone in settings it should alter the reported times, as it thinks you are in a different timezone. But it shouldn’t alter the stored time within a database.

Am I wrong?

[alexjustesen]

Would love to get more input on this, it's been my experience also that anytime you're storing records it should be in UTC and then converted for any views

[bdowden]

100% should be stored in UTC and converted upon displaying data.

[alexdelprete]

I thought UTC as storage time is the way to go. One just uses timezone for UI/frontend.

Absolutely right. In general, the best practice is always: storage layer contains universal format, presentation layer converts data to user preference.

[JourneyOver]

Yes, UTC in storage is the way it's should be. Was mainly making a note (and I was running on like nearly ~35ish hours of no sleep) that it seems that it's just pulling the time straight from from the created_at date instead of doing any actual conversion on the UI side of thing, as no matter what you have the timezone set as in the settings, the results page are just always reported as UTC still.

[alexdelprete]

it seems that it's just pulling the time straight from from the created_at date instead of doing any actual conversion on the UI

That is true, the created_at column is in local time format, not UTC.

as no matter what you have the timezone set as in the settings, the results page are just always reported as UTC still.

that is not what I'm observing in my results page, it's in local time format:

(and I was running on like nearly ~35ish hours of no sleep)

ouch! I hope not for health problems. 35h is a lot without sleeping. Take care.

[JourneyOver]

@alexdelprete Wait your created_at is reporting your local time along with the results page? if you was to change the timezone information on the settings page to say something else (as an example: America/Chicago which is my timezone) and do a test would it change the created at times for you?

as that is not what I'm experiencing at all, I can sit there and mess with the timezone in the settings page and run tests all day and it always comes back to just having the created at on the results page and in the database file for created_at in UTC.

I've destroyed the docker container / database file several times over since the release of v0.1.0-Alpha.10 and this is on an extremely fresh build of ubuntu as well..

EDIT: If it makes any difference I am just using the default database (sqlite) and not setting up a influxdb / mysql database..

[alexdelprete]

as that is not what I'm experiencing at all, I can sit there and mess with the timezone in the settings page and run tests all day and it always comes back to just having the created at on the results page and in the database file for created_at in UTC.

I found out why: look at the results table structure.

The created_at column is a db function. So it gets timestamped by the database, I'm using MariaDB. When I view the records (using HeidiSQL client) I always see local time, probably it's stored as unix epoch time and the client converts it in the UI.

I don't know what happens with sqlite...

[JourneyOver]

@alexdelprete So I downloaded the HeidiSQL client (I've been using SQLite Database Browser any other time) to see the results table structure and see if it was the same or different than yours and it is indeed a bit different...

My defaults for some reason are just no default while it looks like yours is current_timestamp() and your datatype is TIMESTAMP while mine is just DATETIME..

---

This is what SQLite Database Browser shows:

[alexdelprete]

In SQLite DB Browser screenshot default I can see DEFAULT CURRENT_TIMESTAMP. I think it's a universal value, converted by the client you're using. Probably HeidiSQL is converting it when I view data of the table.

Did you check the Data tab in HeidiSQL? How's the value reported?

[JourneyOver]

Did you check the Data tab in HeidiSQL? How's the value reported?

It's still coming back as UTC time, this was also on a fresh speedtest tracker container with a fresh database the only thing I did was went into the setting page on the WebUI and changed it to my timezone and qeued up a test after setting the timezone.

[alexdelprete]

In HeidiSQL there's an inline help for field types:

So, at least if the DB is MariaDB, the value is unix epoch. That means that HeidiSQL is formatting it in the UI.

Probably the TIMESTAMP type doesn't exist in SQLite, and it's handled differently, but it's always UTC.

[JourneyOver]

Yea for some reason I can't get the inline help to come up for anything in the fields type and it doesn't let me edit the field types either for ones that are already created, but if I create a new column and mess around with the datatype field it doesn't even have TIMESTAMP as an option, only DATE, TIME and DATETIME for the temporal (time) spot.. So I think you are correct that the TIMESTAMP type doesn't exist in SQLite at all, so we would need to figure out a different method then for people who use SQLite instead of one of the other options.

[alexdelprete]

we would need to figure out a different method then for people who use SQLite instead of one of the other options.

Why? it's in UTC both on sqlite and MariaDB. And that's what we agreed to do in this thread: UTC in the storage, conversion in the UI.

The difference is only the fact that in my setup, HeidiSQL is showing me those values in local format, at UI level, but data is TIMESTAMP, so epoch format, a universal value.

[JourneyOver]

I mean on the webUI side of things not the database side of things, because obviously something is messing up on the webUI side of things that is causing the timezone to not work correctly for whatever reason still and the only difference I see between me and you (and you said it was working for you just fine on the webui) is that you are using MariaDB while I'm using sqlite.