From patchwork Tue Feb 15 13:40:27 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 5172 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Jyj0471d0z3wtR for ; Tue, 15 Feb 2022 13:40:36 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Jyj0318F4z5QH; Tue, 15 Feb 2022 13:40:35 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Jyj023z4mz2ymg; Tue, 15 Feb 2022 13:40:34 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Jyj012Bldz2yJG for ; Tue, 15 Feb 2022 13:40:33 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Jyj002PDnz1Lk; Tue, 15 Feb 2022 13:40:32 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1644932432; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=35w9E1JKjss8A/vv24jCEQ+WhUIKdodWgqON0HGTx2c=; b=BxpAIjN7T9qM6jIPAxw71G/C4WQNQv6rBAIDH0psh/uNteyDSWgkR8uwDgsdkIZSnCo6aN 216YwQ1c6VeSZfDQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1644932432; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=35w9E1JKjss8A/vv24jCEQ+WhUIKdodWgqON0HGTx2c=; b=ailPPuUyY9+H7ZjAIetapAxdFiScQxO37kYGSminN5BlbX28R4P/erQ5OY0PqAeURxg59f MwU5PMsao3GbZCMy5ZbCO2PfE10kM6iYEZEi1/kd+8zSjnQKYvF/OkhMufhJr4nAe52Ugc srqHy2w702tM1wOrtCvZyhXEzSCguWtBdZy8iVz89x6eRdJcmQKrQeCtNScpJx5+z3Vu/T sNzJ2eLsZUjd4GrBccL020VjVxGNjlUS/t1W9qjFWnd7ZV3+MUHIEQTL0VuYfoafL4YjVW 3baxr1WXfrXkujWeqmGyu0XBDYWqovRzxYQ2W0wlZRvQK1cRfbhszQPujs1oOQ== From: Michael Tremer 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 X-BeenThere: development@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: IPFire development talk List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: Michael Tremer , Jon Murphy Errors-To: development-bounces@lists.ipfire.org Sender: "Development" 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 Fixes: #12768 Tested-by: Jon Murphy Signed-off-by: Michael Tremer --- 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/ovpnclients.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 = qq( common_name, SUM( STRFTIME('%s', ( CASE - WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + WHEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') THEN DATETIME(COALESCE(disconnected_at, CURRENT_TIMESTAMP), 'localtime') - ELSE DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + ELSE DATETIME('$to_datestring', 'start of day', '+86399 seconds') END ), 'utc') - STRFTIME('%s', ( CASE - WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + WHEN DATETIME(connected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') THEN DATETIME(connected_at, 'localtime') - ELSE DATETIME('$from_datestring', 'localtime', 'start of day') + ELSE DATETIME('$from_datestring', 'start of day') END ), 'utc') ) AS duration @@ -133,10 +133,10 @@ my $database_query = qq( ( disconnected_at IS NULL OR - DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') ) AND - DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', '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 = '$cgiparams{"CONNECTION_NAME"}' AND ( - DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'localtime', 'start of day') + DATETIME(disconnected_at, 'localtime') > DATETIME('$from_datestring', 'start of day') AND - DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'localtime', 'start of day', '+86399 seconds') + DATETIME(connected_at, 'localtime') < DATETIME('$to_datestring', 'start of day', '+86399 seconds') ) ORDER BY connected_at; );