opendmarc-reports issues for timezones ahead of UTC

[yoyekyoyek]

For timezones that are ahead of UTC using --utc and --day options causes wrong dates in reported messages (the messages are not from yesterday, but for the day before yesterday).

My native zone:

mysql> SET TIME_ZONE='+01:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select FROM_UNIXTIME('1647385200');
+-----------------------------+
| FROM_UNIXTIME('1647385200') |
+-----------------------------+
| 2022-03-16 00:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME('1647471600');
+-----------------------------+
| FROM_UNIXTIME('1647471600') |
+-----------------------------+
| 2022-03-17 00:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select date from messages where DATE(messages.date) >= DATE(FROM_UNIXTIME('1647385200')) AND DATE(messages.date) < DATE(FROM_UNIXTIME('1647471600')) limit 5;
+---------------------+
| date                |
+---------------------+
| 2022-03-16 05:32:21 |
| 2022-03-16 06:32:04 |
| 2022-03-16 07:10:51 |
| 2022-03-16 07:19:19 |
| 2022-03-16 07:32:51 |
+---------------------+
5 rows in set (0.00 sec)

UTC Zone:

mysql> SET TIME_ZONE='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select FROM_UNIXTIME('1647385200');
+-----------------------------+
| FROM_UNIXTIME('1647385200') |
+-----------------------------+
| 2022-03-15 23:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME('1647471600');
+-----------------------------+
| FROM_UNIXTIME('1647471600') |
+-----------------------------+
| 2022-03-16 23:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select date from messages where DATE(messages.date) >= DATE(FROM_UNIXTIME('1647385200')) AND DATE(messages.date) < DATE(FROM_UNIXTIME('1647471600')) limit 5;
+---------------------+
| date                |
+---------------------+
| 2022-03-15 04:37:45 |
| 2022-03-15 05:23:46 |
| 2022-03-15 05:24:57 |
| 2022-03-15 07:01:43 |
| 2022-03-15 07:55:24 |
+---------------------+
5 rows in set (0.01 sec)

What I would suggest:
That would be easily resolved by changing the DATE to TIMESTAMP (DATE shows only a specific day, which as shown can lead to problems).
Result of query with TIMESTAMP instead of DATE:

mysql> SET TIME_ZONE='+00:00';
Query OK, 0 rows affected (0.00 sec)

mysql> select FROM_UNIXTIME('1647385200');
+-----------------------------+
| FROM_UNIXTIME('1647385200') |
+-----------------------------+
| 2022-03-15 23:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select FROM_UNIXTIME('1647471600');
+-----------------------------+
| FROM_UNIXTIME('1647471600') |
+-----------------------------+
| 2022-03-16 23:00:00.000000  |
+-----------------------------+
1 row in set (0.00 sec)

mysql> select date from messages where TIMESTAMP(messages.date) >= TIMESTAMP(FROM_UNIXTIME('1647385200')) AND TIMESTAMP(messages.date) < TIMESTAMP(FROM_UNIXTIME('1647471600')) limit 5;
+---------------------+
| date                |
+---------------------+
| 2022-03-16 04:32:21 |
| 2022-03-16 05:32:04 |
| 2022-03-16 06:10:51 |
| 2022-03-16 06:19:19 |
| 2022-03-16 06:32:51 |
+---------------------+
5 rows in set (0.00 sec)

This way we're tied to date and time limits, not only a day of month.