From 5eba322cbd24191e05304df08c80af846977d99b Mon Sep 17 00:00:00 2001
From: Alexandre Alapetite <alexandre@alapetite.fr>
Date: Tue, 14 Oct 2025 11:05:17 +0200
Subject: New stats overview of dates with most unread articles (#8089)

New view with direct links to dates with most unread articles:

<img width="734" height="581" alt="image" src="https://github.com/user-attachments/assets/159a39b3-3a06-4ae9-9cc0-62ae36d9db9c" />
---
 app/Models/StatsDAOPGSQL.php | 14 +++++++++++++-
 1 file changed, 13 insertions(+), 1 deletion(-)

(limited to 'app/Models/StatsDAOPGSQL.php')

diff --git a/app/Models/StatsDAOPGSQL.php b/app/Models/StatsDAOPGSQL.php
index 5e3476808..c4a5e5c05 100644
--- a/app/Models/StatsDAOPGSQL.php
+++ b/app/Models/StatsDAOPGSQL.php
@@ -3,6 +3,17 @@ declare(strict_types=1);
 
 class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
 
+	#[\Override]
+	protected function sqlDateToIsoGranularity(string $field, int $precision, string $granularity): string {
+		$offset = $this->getTimezoneOffset();
+		return match ($granularity) {
+			'day' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY-MM-DD')",
+			'month' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY-MM')",
+			'year' => "to_char(to_timestamp(($field / $precision) + $offset), 'YYYY')",
+			default => throw new InvalidArgumentException('Invalid date granularity'),
+		};
+	}
+
 	/**
 	 * Calculates the number of article per hour of the day per feed
 	 *
@@ -43,8 +54,9 @@ class FreshRSS_StatsDAOPGSQL extends FreshRSS_StatsDAO {
 		if ($feed) {
 			$restrict = "WHERE e.id_feed = {$feed}";
 		}
+		$offset = $this->getTimezoneOffset();
 		$sql = <<<SQL
-SELECT extract( {$period} from to_timestamp(e.date)) AS period
+SELECT extract( {$period} from to_timestamp(e.date + {$offset})) AS period
 , COUNT(1) AS count
 FROM `_entry` AS e
 {$restrict}
-- 
cgit v1.2.3