Improve date handling

[dlangille]

The problem

Existing database queries use NOW() , UNIX_TIMESTAMP, etc. This makes it difficult to cater for different timezones. Case in point: the graphs can display UTC (or whatever timezone you choose), but the date picker on graph is always local time (for example).

Output of ./validate.php

n/a

What was the last working version of LibreNMS?

No response

Anything in the logs that might be useful for us?

Here is a starting point:

% rg "(\bDATE_|NOW()|UNIX_TIMESTAMP|CONVERT_TZ|\bDATE(|FROM_UNIXTIME)" -t php -c
daily.php:11
billing-calculate.php:2
discovery.php:1
poll-billing.php:3
LibreNMS/Billing.php:5
includes/services.inc.php:1
includes/functions.php:1
LibreNMS/Validations/Database/CheckSqlServerTime.php:1
includes/html/api_functions.inc.php:1
includes/html/print-graph-alerts.inc.php:1
includes/polling/functions.inc.php:2
includes/html/table/poll-log.inc.php:1
includes/html/table/bills.inc.php:1
includes/html/pages/bills.inc.php:1
includes/polling/ipmi.inc.php:1
includes/html/table/alertlog.inc.php:2
includes/html/pages/bill.inc.php:11
includes/html/table/alerts.inc.php:1
includes/html/table/eventlog.inc.php:1
includes/html/table/alertlog-stats.inc.php:1
includes/polling/customoid.inc.php:1
includes/html/reports/alert-log.pdf.inc.php:1
includes/html/pages/peering/as-selection.inc.php:1
includes/discovery/route.inc.php:4
includes/polling/applications/proxmox.inc.php:2
LibreNMS/Device/Sensor.php:1
includes/html/pages/device/port/events.inc.php:1
includes/html/pages/device/overview/syslog.inc.php:1
includes/html/pages/device/overview/eventlog.inc.php:1
includes/html/pages/bill/transfer.inc.php:10
database/migrations/2020_04_08_172357_alert_schedule_utc.php:8
includes/discovery/route/routeros.inc.php:1
includes/html/graphs/bill/historicbits.inc.php:1
app/Http/Controllers/Table/AlertScheduleController.php:2
app/Models/PollerCluster.php:2
app/Models/Poller.php:2
app/Http/Controllers/Table/DeviceController.php:1
app/Http/Controllers/Device/Tabs/LatencyController.php:1

[eskyuu]

My thoughts are that everything on the server should either be a unix timestamp (which are by nature timezone agnostic), or stored in the server timezone which is checked as part of the server validation. The translation needs to happen whenever a date is sent to or from a web page, converting to the user timezone when displayed, and converting to the server timezone when read from the browser.

Anything done on the CLI or server end (daily.php) will continue to use the server time, and all entries in the database should be stored in the server time.

So having said this, many of the files listed above should not be an issue. Is it possible to make a list within this ticket that we can tick off after checking each file?