From mboxrd@z Thu Jan 1 00:00:00 1970
From: Michael Tremer <michael.tremer@ipfire.org>
To: development@lists.ipfire.org
Subject: [PATCH] ovpnclients.dat: Fix adjusting input dates
Date: Tue, 15 Feb 2022 13:40:27 +0000
Message-ID: <20220215134027.773437-1-michael.tremer@ipfire.org>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="===============7209218712378746553=="
List-Id: <development.lists.ipfire.org>
--===============7209218712378746553==
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
This patch changes that we no longer interpret any dates put in by the
user as UTC. They used to be converted into localtime because, although
they have already been in local time.
This went unnoticed since in Europe we are close (enough) to UTC that
there is no significant discrepancy on the report. However, being in
North America is enough to generate confusing reports.
Reported-by: Paul <kairis(a)gmail.com>
Fixes: #12768
Tested-by: Jon Murphy <jon.murphy(a)ipfire.org>
Signed-off-by: Michael Tremer <michael.tremer(a)ipfire.org>
---
html/cgi-bin/logs.cgi/ovpnclients.dat | 16 ++++++++--------
1 file changed, 8 insertions(+), 8 deletions(-)
diff --git a/html/cgi-bin/logs.cgi/ovpnclients.dat b/html/cgi-bin/logs.cgi/ov=
pnclients.dat
index 5e2c1ff49..100573214 100755
--- a/html/cgi-bin/logs.cgi/ovpnclients.dat
+++ b/html/cgi-bin/logs.cgi/ovpnclients.dat
@@ -115,16 +115,16 @@ my $database_query =3D qq(
common_name, SUM(
STRFTIME('%s', (
CASE
- WHEN DATETIME(COALESCE(disconnected_at, CURR=
ENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start=
of day', '+86399 seconds')
+ WHEN DATETIME(COALESCE(disconnected_at, CURR=
ENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'start of day', '+8=
6399 seconds')
THEN DATETIME(COALESCE(disconnected_=
at, CURRENT_TIMESTAMP), 'localtime')
- ELSE DATETIME('$to_datestring', 'loc=
altime', 'start of day', '+86399 seconds')
+ ELSE DATETIME('$to_datestring', 'sta=
rt of day', '+86399 seconds')
END
), 'utc') -
STRFTIME('%s', (
CASE
- WHEN DATETIME(connected_at, 'localtime') > D=
ATETIME('$from_datestring', 'localtime', 'start of day')
+ WHEN DATETIME(connected_at, 'localtime') > D=
ATETIME('$from_datestring', 'start of day')
THEN DATETIME(connected_at, 'localti=
me')
- ELSE DATETIME('$from_datestring', 'l=
ocaltime', 'start of day')
+ ELSE DATETIME('$from_datestring', 's=
tart of day')
END
), 'utc')
) AS duration
@@ -133,10 +133,10 @@ my $database_query =3D qq(
(
disconnected_at IS NULL
OR
- DATETIME(disconnected_at, 'localtime') > DATETIME('$from_da=
testring', 'localtime', 'start of day')
+ DATETIME(disconnected_at, 'localtime') > DATETIME('$from_da=
testring', 'start of day')
)
AND
- DATETIME(connected_at, 'localtime') < DATETIME('$to_datestri=
ng', 'localtime', 'start of day', '+86399 seconds')
+ DATETIME(connected_at, 'localtime') < DATETIME('$to_datestri=
ng', 'start of day', '+86399 seconds')
GROUP BY common_name
ORDER BY common_name, duration DESC;
);
@@ -148,9 +148,9 @@ if ($cgiparams{'CONNECTION_NAME'}) {
WHERE
common_name =3D '$cgiparams{"CONNECTION_NAME"}'
AND (
- DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'l=
ocaltime', 'start of day')
+ DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 's=
tart of day')
AND
- DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localt=
ime', 'start of day', '+86399 seconds')
+ DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start =
of day', '+86399 seconds')
)
ORDER BY connected_at;
);
--=20
2.30.2
--===============7209218712378746553==--