cachet: SQL errors with Metrics rendering in Cachet 2.4-dev and Postgres 10.

Fresh Cachet 2.4-dev install with PostgreSQL 10 (and a table prefix of cachet_). Any attempts to have a Metric item’s graph displayed have an image loading placeholder, and the Laravel log fills up with this:

LINE 1: SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD ...
                       ^ (SQL: SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD HH24:00') AS key, sum(cachet_metric_points.value * cachet_metric_points.counter) AS value FROM cachet_metrics INNER JOIN cachet_metric_points ON cachet_metrics.id = cachet_metric_points.metric_id WHERE cachet_metrics.id = :metricId AND cachet_metric_points.created_at >= (NOW() - INTERVAL '12' HOUR) AND cachet_metric_points.created_at <= NOW() GROUP BY to_char(cachet_metric_points.created_at, 'HH24:00') ORDER BY to_char(cachet_metric_points.created_at, 'HH24:00')) in /var/www/Cachet/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664

There is a full stack trace, but the problem is in the SQL, so I’m omitting it.

There’s two separate problems here.

The first is that with recent PostgreSQL versions, it’s not quite smart enough to identify the to_char() call as a key, so you have to explicitly state it in the GROUP BY clause, like this:

SELECT to_char(cachet_metric_points.created_at, 'YYYY-MM-DD HH24:00') AS key, sum(cachet_metric_points.value * cachet_metric_points.counter) AS value FROM cachet_metrics INNER JOIN cachet_metric_points ON cachet_metrics.id = cachet_metric_points.metric_id WHERE cachet_metrics.id = <INSERT_ID_HERE> AND cachet_metric_points.created_at >= (NOW() - INTERVAL '12' HOUR) AND cachet_metric_points.created_at <= NOW() GROUP BY to_char(cachet_metric_points.created_at, 'HH24:00'), cachet_metric_points.created_at ORDER BY to_char(cachet_metric_points.created_at, 'HH24:00');

That’s an easy fix.

But there’s another problem which is preventing it from returning data. This system that I’m running it on is freshly installed, and I only just created data this morning, so the created_at values are very recent:

SELECT * FROM cachet_metric_points;
 id | metric_id | value  |     created_at      |     updated_at      | counter
----+-----------+--------+---------------------+---------------------+---------
  1 |         1 | 42.000 | 2018-09-24 18:15:30 | 2018-09-24 12:15:57 |       3
  2 |         1 | 38.000 | 2018-09-24 18:50:30 | 2018-09-24 12:50:43 |       1
  3 |         1 | 37.000 | 2018-09-24 18:50:30 | 2018-09-24 12:50:46 |       2
  4 |         1 | 40.000 | 2018-09-24 18:51:00 | 2018-09-24 12:50:47 |       1
  5 |         1 | 35.000 | 2018-09-24 18:51:00 | 2018-09-24 12:50:48 |       1
(5 rows)

created_at appears stored in UTC and updated_at is stored at local time, which makes the <= NOW() part of the WHERE clause clobber those data points. This may be because I’m passing in data points with a local epoch time (that’s Central, while the server is Mountain)… but I don’t think that would (should?) cause issues like that. Either way, the results of my query that I added the GROUP BY fix to are blank, and that doesn’t seem right.

Eventually, of course, time will move past that created_at value, and it should start returning results, but this seems a bit off to me.