I have the following (postgress) SQL Query:
WITH cal AS(
SELECT ts AS t_begin, ts + '6hours'::interval AS t_end
FROM generate_series('2021-05-09 00:00:00'::timestamp
, '2021-06-15 09:51:34', '6hours'::interval) ts
)
SELECT d.variable_id, cal.t_end AS date_time, d.sample_count, d.sample_period_ms, MIN(d.min_value) AS min_value, MAX(d.max_value) AS max_value, AVG(d.value) AS value
FROM cal
LEFT JOIN public.fp_data d
ON d.date_time >= cal.t_begin
AND d.date_time < cal.t_end
AND variable_id = 15
GROUP BY cal.t_end, d.variable_id, d.sample_count, d.sample_period_ms
ORDER BY cal.t_end
And what it does is it creates a series of 6 hours per day and takes averages from it. A result may look like this:
| variable_id | date_time | sample_count | sample_period_ms | min_value | max_value | value |
|---|---|---|---|---|---|---|
| 15 | 2021-06-06 06:00:00 | 120 | 59577 | -1.4960686 | 1.1995025 | 0.30439844254136744 |
| 15 | 2021-06-06 12:00:00 | 120 | 59577 | -1.4887594 | 1.1997863 | 0.30570657099738263 |
| 15 | 2021-06-06 18:00:00 | 120 | 59577 | -1.4972655 | 1.1999407 | 0.30465021305485984 |
| 15 | 2021-06-07 00:00:00 | 120 | 59577 | -1.4703176 | 1.1985717 | 0.30615092198218197 |
| 15 | 2021-06-07 06:00:00 | 120 | 59577 | -1.4983453 | 1.1998215 | 0.3049584258111712 |
| 15 | 2021-06-07 12:00:00 | 120 | 59577 | -1.4996965 | 1.1996177 | 0.3047593224032149 |
| 15 | 2021-06-07 18:00:00 | 120 | 59577 | -1.4949534 | 1.1998252 | 0.30591585460444787 |
| 15 | 2021-06-08 00:00:00 | 120 | 59577 | -1.4997886 | 1.1995926 | 0.30432341914644556 |
| 15 | 2021-06-08 06:00:00 | 120 | 59577 | -1.4956167 | 1.1996672 | 0.3085149774948756 |
| 15 | 2021-06-08 12:00:00 | 120 | 59577 | -1.4986398 | 1.1998078 | 0.30561149754247613 |
| 15 | 2021-06-08 18:00:00 | 120 | 59577 | -1.499255 | 1.1990205 | 0.3064040885648123 |
| 15 | 2021-06-09 00:00:00 | 120 | 59577 | -1.4864591 | 1.1998134 | 0.3057553283664403 |
| NULL | 2021-06-09 06:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-09 12:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-09 18:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-10 00:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-10 06:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-10 12:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-10 18:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-11 00:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-11 06:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-11 12:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-11 18:00:00 | NULL | NULL | NULL | NULL | NULL |
| NULL | 2021-06-12 00:00:00 | NULL | NULL | NULL | NULL | NULL |
I have implemented this query as follows in my JPA interface:
@Transactional
public interface FloatingPointDataRepos extends CrudRepository<FloatingPointData, Integer> {
List<FloatingPointData> findAllByVariable_IdAndDateTimeBetween(Integer variableId, Timestamp startTimeFrame, Timestamp endTimeFrame);
@Query(value = "WITH cal AS( "+
"SELECT ts AS t_begin, ts + '6hours'\:\:interval AS t_end "+
"FROM generate_series(:startTimeFrame\:\:timestamp "+
", :endTimeFrame, '6hours'\:\:interval) ts "+
") "+
"SELECT d.variable_id, cal.t_end AS date_time, d.sample_count, d.sample_period_ms, MIN(d.min_value) AS min_value, MAX(d.max_value) AS max_value, AVG(d.value) AS value "+
"FROM cal "+
"LEFT JOIN public.fp_data d "+
"ON d.date_time >= cal.t_begin "+
"AND d.date_time < cal.t_end "+
"AND variable_id = :variableId " +
"GROUP BY cal.t_end, d.variable_id, d.sample_count, d.sample_period_ms "+
"ORDER BY cal.t_end", nativeQuery = true)
List<FloatingPointData> findAllByVariableId(Integer variableId, Timestamp startTimeFrame, Timestamp endTimeFrame);
}
The code works, but the problem is that it returns null for the rows where the variable_id and other values are not set.
A sample of what it now returns is as follows:
object object object object object object object object object object null null null null null null
I want it to always return the date_time in the object, like you see in the table above.
How do I fix this?
Advertisement
Answer
variable_id is a primary key. When variable_id was null, Hibernate returned null instead of the object.
The fix was to change SELECT d.variable_id to SELECT :variableId AS variable_id