Incorrect publishing times

[cr04ch]

Selfoss version	2.20-f04dbca
DB type	SQLite

Selfoss shows me the publication time of the items as 4 hours earlier than the real value. The numeric time zone for both client side and server side is +0200.

An example:
Date in feed	Fri, 30 Jun 2023 07:54:04 +0200
Date in selfoss.db	2023-06-30 05:54:04
Date in JSON (when loading the page)	2023-06-30T05:54:04+02:00
Date displayed	2023. 06. 30. 3:54:04

As you can see, the time is not displayed in local time (which is what I would expect), but it is also not displayed in UTC time, but an incorrectly calculated time is displayed.

[jtojnar]

Thanks for reporting. I will try to look into it deeper during the weekend.

I believe we are currently unfortunately using local time in the database but it can happen that UTC is used when there is timezone misalignment between the web and cli PHP configs, see #1046

But the fact that the date is converted to UTC when displayed is unfortunate as well.

[jtojnar]

I just tried this on 483f4eee81c5d7ded980202a56b6f605b5d1fbca (no relevant changes since f04dbca79b4b6d6527b954d5f1b48c72696b86e1) with SQLite and am unable to reproduce it:

Date in feed	Sun, 23 Jul 2023 11:39:00 GMT
Date in selfoss.db	2023-07-23 11:30:00
Date in JSON (when loading the page)	2023-07-23T11:39:00+00:00
Date displayed	3 mins ago

I also tried modifying the feed to use UTC+02:00 but there was no difference. In both cases the SQLite database stored the time in UTC and JSON returned it as such:

Feed source

```xml http://example.com/ RSS for Node Sun, 23 Jul 2023 13:47:25 +0200 Sun, 23 Jul 2023 13:47:00 +0200 1 http://example.com/test/1690112820 http://example.com/test/1690112820 Sun, 23 Jul 2023 13:47:00 +0200 http://example.com/test/1690112760 http://example.com/test/1690112760 Sun, 23 Jul 2023 13:46:00 +0200 http://example.com/test/1690112700 http://example.com/test/1690112700 Sun, 23 Jul 2023 13:45:00 +0200 http://example.com/test/1690112640 http://example.com/test/1690112640 Sun, 23 Jul 2023 13:44:00 +0200 http://example.com/test/1690112580 http://example.com/test/1690112580 Sun, 23 Jul 2023 13:43:00 +0200 http://example.com/test/1690112520 http://example.com/test/1690112520 Sun, 23 Jul 2023 13:42:00 +0200 http://example.com/test/1690112460 http://example.com/test/1690112460 Sun, 23 Jul 2023 13:41:00 +0200 http://example.com/test/1690112400 http://example.com/test/1690112400 Sun, 23 Jul 2023 13:40:00 +0200 http://example.com/test/1690112340 http://example.com/test/1690112340 Sun, 23 Jul 2023 13:39:00 +0200 http://example.com/test/1690112280 http://example.com/test/1690112280 Sun, 23 Jul 2023 13:38:00 +0200 ```

The relevant client code is:

https://github.com/fossar/selfoss/blob/483f4eee81c5d7ded980202a56b6f605b5d1fbca/client/js/templates/Item.jsx#L490 https://github.com/fossar/selfoss/blob/483f4eee81c5d7ded980202a56b6f605b5d1fbca/client/js/requests/items.js#L61 https://github.com/fossar/selfoss/blob/483f4eee81c5d7ded980202a56b6f605b5d1fbca/client/js/requests/items.js#L5

1. What web browser do you use?
2. What do you get when you enter new Date('2023-06-30T05:54:04+02:00').toLocaleString() into web browser’s console (typically opened with F12, possibly you will need to switch to Console tab). I am seeing "30/06/2023, 05:54:04" as I would expect.

[cr04ch]

Sorry, it seems that I must be looking for the error somewhere in my Firefox profile.

The new Date('2023-06-30T05:54:04+02:00').toLocaleString() command in Firefox 115.0.2 with my original profile returned 2023. 06. 30. 3:54:04. In Chromium 115.0.5790.102, or Firefox started with a clean profile, it returned 2023. 06. 30. 5:54:04.

However, if the time is always stored in UTC in the database, then the JSON file should contain it in that format, right? The output of the new Date('2023-06-30T05:54:04+00:00').toLocaleString()command is 2023. 06. 30. 7:54:04, and this is the value that selfoss should display according to my local time. Or am I wrong?

[jtojnar]

However, if the time is always stored in UTC in the database, then the JSON file should contain it in that format, right? The output of the new Date('2023-06-30T05:54:04+00:00').toLocaleString()command is 2023. 06. 30. 7:54:04, and this is the value that selfoss should display according to my local time. Or am I wrong?

We should have been storing local time in the SQLite database because we have not come up with a migration path yet. But it looks like we accidentally switched to UTC (likely in efae04199a02cdd39d2dec89047895493bf16046) so migration is no longer possible (we have no way of knowing which entries were stored in the db before/after the change). But it probably does not matter if older items are few hours off so we can just switch to printing it as UTC.

[cr04ch]

I tried the Lorem ipsum feed, and selfoss showed me the latest posts as 2 hours ago. The database stores the correct UTC time, but in the JSON file it is not marked as UTC time, but incorrectly as CEST (+02:00).
Feed	Sun, 23 Jul 2023 20:46:00 GMT
DB	2023-07-23 20:46:00
JSON	2023-07-23T20:46:00+02:00

[cr04ch]

If I set date.timezone=UTC in the php.ini file on the server, selfoss shows that the last posts are only a few minutes old and the JSON file contains the times in UTC.

This way, although selfoss works correctly, the php.ini file does not contain the correct time zone.

[davidoskky]

There definitely is a parsing error performed by selfoss related to the timezone. I am using sqlite. I would agree that for items older than a day a few hours misalignment is not a big deal, thus eventual problems will be only present in the first day after upgrading.

I also tested the feed above. Article date: 2023-07-24T00:45:00Z Item datetime: 2023-07-24T00:45:00-04:00

[cr04ch]

I did some tests with the two settings. The server's OS time zone is CEST, I just changed the date.timezone setting in php.ini.

I marked the incorrect values in italic and the correct values in bold, assuming that we want to store the UTC time in the database and expect a value in the JSON file that will be used by selfoss to display the time according to the client's local time zone.

date.timezone=Europe/Budapest	Feed	DB	JSON
Mon, 24 Jul 2023 14:13:21 +0000	2023-07-24 14:13:21	2023-07-24T14:13:21+02:00
Mon, 24 Jul 2023 16:13:21 +0200	2023-07-24 14:13:21	2023-07-24T14:13:21+02:00
No PubDate	2023-07-24 16:13:21	2023-07-24T16:13:21+02:00

date.timezone=UTC	Feed	DB	JSON
Mon, 24 Jul 2023 14:45:46 +0000	2023-07-24 14:45:46	2023-07-24T14:45:46+00:00
Mon, 24 Jul 2023 16:45:46 +0200	2023-07-24 14:45:46	2023-07-24T14:45:46+00:00
No PubDate	2023-07-24 14:45:46	2023-07-24T14:45:46+00:00

From the results of these tests, it seems to me that selfoss only works consistently with the UTC time zone, with other settings either the database or the JSON file will contain incorrect data.

[jtojnar]

Thanks. That is helpful. I opened a PR with a possible fix. Currently untested, will try to test it later in the week. There is quite a few things to check, which is why I have not attempted to fix it until now.

[cr04ch]

Thanks! I retested after applying the changes, using the previous method. It seems that if the publication date is not specified in the feed, it still adds incorrect data to the database. But the JSON file now contains UTC dates (but with an incorrect value in the last case).

date.timezone=Europe/Budapest	Feed	DB	JSON
Tue, 25 Jul 2023 07:36:39 +0000	2023-07-25 07:36:39	2023-07-25T07:36:39+00:00
Tue, 25 Jul 2023 09:36:39 +0200	2023-07-25 07:36:39	2023-07-25T07:36:39+00:00
No PubDate	2023-07-25 09:36:39	2023-07-25T09:36:39+00:00

[jtojnar]

Thanks. I pushed a likely fix. Will still need to test it with MySQL, though.

[cr04ch]

Thanks. With the recent fixes, using SQLite now works correctly for me.