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 483f4ee (no relevant changes since f04dbca) 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 version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:dc="http://purl.org/dc/elements/1.1/">
<channel>
<title>
<![CDATA[Lorem ipsum feed for an interval of 1 minutes with 10 item(s)]]>
</title>
<description>
<![CDATA[This is a constantly updating lorem ipsum feed]]>
</description>
<link>http://example.com/</link>
<generator>RSS for Node</generator>
<lastBuildDate>Sun, 23 Jul 2023 13:47:25 +0200</lastBuildDate>
<pubDate>Sun, 23 Jul 2023 13:47:00 +0200</pubDate>
<copyright>
<![CDATA[Michael Bertolacci, licensed under a Creative Commons Attribution 3.0 Unported License.]]>
</copyright>
<ttl>1</ttl>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:47:00+02:00]]>
</title>
<description>
<![CDATA[Laborum non culpa do qui in cupidatat qui consequat occaecat anim veniam pariatur.]]>
</description>
<link>http://example.com/test/1690112820</link>
<guid isPermaLink="true">http://example.com/test/1690112820</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:47:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:46:00+02:00]]>
</title>
<description>
<![CDATA[Nulla nostrud mollit culpa commodo mollit consequat anim.]]>
</description>
<link>http://example.com/test/1690112760</link>
<guid isPermaLink="true">http://example.com/test/1690112760</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:46:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:45:00+02:00]]>
</title>
<description>
<![CDATA[Consequat non nisi aliquip nisi nisi anim sint occaecat laboris voluptate fugiat duis.]]>
</description>
<link>http://example.com/test/1690112700</link>
<guid isPermaLink="true">http://example.com/test/1690112700</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:45:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:44:00+02:00]]>
</title>
<description>
<![CDATA[Excepteur culpa irure excepteur adipisicing exercitation veniam reprehenderit voluptate mollit eiusmod nostrud cillum amet cillum.]]>
</description>
<link>http://example.com/test/1690112640</link>
<guid isPermaLink="true">http://example.com/test/1690112640</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:44:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:43:00+02:00]]>
</title>
<description>
<![CDATA[Ullamco exercitation duis voluptate tempor.]]>
</description>
<link>http://example.com/test/1690112580</link>
<guid isPermaLink="true">http://example.com/test/1690112580</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:43:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:42:00+02:00]]>
</title>
<description>
<![CDATA[Ea labore labore proident enim aliquip nulla ullamco ullamco dolor fugiat dolore nisi exercitation.]]>
</description>
<link>http://example.com/test/1690112520</link>
<guid isPermaLink="true">http://example.com/test/1690112520</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:42:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:41:00+02:00]]>
</title>
<description>
<![CDATA[Deserunt fugiat amet deserunt nulla.]]>
</description>
<link>http://example.com/test/1690112460</link>
<guid isPermaLink="true">http://example.com/test/1690112460</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:41:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:40:00+02:00]]>
</title>
<description>
<![CDATA[Nulla id proident quis esse aute esse.]]>
</description>
<link>http://example.com/test/1690112400</link>
<guid isPermaLink="true">http://example.com/test/1690112400</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:40:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:39:00+02:00]]>
</title>
<description>
<![CDATA[Sit commodo voluptate ullamco adipisicing laboris.]]>
</description>
<link>http://example.com/test/1690112340</link>
<guid isPermaLink="true">http://example.com/test/1690112340</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:39:00 +0200</pubDate>
</item>
<item>
<title>
<![CDATA[Lorem ipsum 2023-07-23T13:38:00+02:00]]>
</title>
<description>
<![CDATA[Ullamco Lorem aliqua aliquip nisi non.]]>
</description>
<link>http://example.com/test/1690112280</link>
<guid isPermaLink="true">http://example.com/test/1690112280</guid>
<dc:creator>
<![CDATA[John Smith]]>
</dc:creator>
<pubDate>Sun, 23 Jul 2023 13:38:00 +0200</pubDate>
</item>
</channel>
</rss>

The relevant client code is:

selfoss/client/js/templates/Item.jsx

Line 490 in 483f4ee

{relDate !== null ? relDate : item.datetime.toLocaleString()}

selfoss/client/js/requests/items.js

Line 61 in 483f4ee

datetime: safeDate(entry.datetime),

selfoss/client/js/requests/items.js

Line 5 in 483f4ee

const date = new Date(datetimeString);

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 efae041) 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.