Examples with PGInterval org.postgresql.util.PGInterval used on opensource projects

Example 1 with PGInterval

use of org.postgresql.util.PGInterval in project openremote by openremote.

the class AssetDatapointService method aggregateDatapoints.

public NumberDatapoint[] aggregateDatapoints(AssetAttribute attribute, DatapointInterval datapointInterval, long timestamp) {
    LOG.fine("Aggregating datapoints for: " + attribute);
    AttributeRef attributeRef = attribute.getReferenceOrThrow();
    return persistenceService.doReturningTransaction(entityManager -> entityManager.unwrap(Session.class).doReturningWork(new AbstractReturningWork<NumberDatapoint[]>() {

        @Override
        public NumberDatapoint[] execute(Connection connection) throws SQLException {
            String truncateX;
            String step;
            String interval;
            Function<Timestamp, String> labelFunction;
            SimpleDateFormat dayFormat = new SimpleDateFormat("dd. MMM yyyy");
            SimpleDateFormat timeFormat = new SimpleDateFormat("HH:mm");
            switch(datapointInterval) {
                case HOUR:
                    truncateX = "minute";
                    step = "1 minute";
                    interval = "1 hour";
                    labelFunction = timeFormat::format;
                    break;
                case DAY:
                    truncateX = "hour";
                    step = "1 hour";
                    interval = "1 day";
                    labelFunction = timeFormat::format;
                    break;
                case WEEK:
                    truncateX = "day";
                    step = "1 day";
                    interval = "7 day";
                    labelFunction = dayFormat::format;
                    break;
                case MONTH:
                    truncateX = "day";
                    step = "1 day";
                    interval = "1 month";
                    labelFunction = dayFormat::format;
                    break;
                case YEAR:
                    truncateX = "month";
                    step = "1 month";
                    interval = "1 year";
                    labelFunction = dayFormat::format;
                    break;
                default:
                    throw new IllegalArgumentException("Can't handle interval: " + datapointInterval);
            }
            StringBuilder query = new StringBuilder();
            query.append("select TS as X, coalesce(AVG_VALUE, null) as Y " + " from ( " + "       select date_trunc(?, GS)::timestamp TS " + "       from generate_series(to_timestamp(?) - ?, to_timestamp(?), ?) GS " + "       ) TS " + "  left join ( " + "       select " + "           date_trunc(?, to_timestamp(TIMESTAMP / 1000))::timestamp as TS, ");
            switch(attribute.getTypeOrThrow().getValueType()) {
                case NUMBER:
                    query.append(" AVG(VALUE::text::numeric) as AVG_VALUE ");
                    break;
                case BOOLEAN:
                    query.append(" AVG(case when VALUE::text::boolean is true then 1 else 0 end) as AVG_VALUE ");
                    break;
                default:
                    throw new IllegalArgumentException("Can't aggregate number datapoints for type of: " + attribute);
            }
            query.append(" from ASSET_DATAPOINT " + "         where " + "           to_timestamp(TIMESTAMP / 1000) >= to_timestamp(?) - ? " + "           and " + "           to_timestamp(TIMESTAMP / 1000) <= to_timestamp(?) " + "           and " + "           ENTITY_ID = ? and ATTRIBUTE_NAME = ? " + "         group by TS " + "  ) DP using (TS) " + " order by TS asc ");
            PreparedStatement st = connection.prepareStatement(query.toString());
            long timestampSeconds = timestamp / 1000;
            st.setString(1, truncateX);
            st.setLong(2, timestampSeconds);
            st.setObject(3, new PGInterval(interval));
            st.setLong(4, timestampSeconds);
            st.setObject(5, new PGInterval(step));
            st.setString(6, truncateX);
            st.setLong(7, timestampSeconds);
            st.setObject(8, new PGInterval(interval));
            st.setLong(9, timestampSeconds);
            st.setString(10, attributeRef.getEntityId());
            st.setString(11, attributeRef.getAttributeName());
            try (ResultSet rs = st.executeQuery()) {
                List<NumberDatapoint> result = new ArrayList<>();
                while (rs.next()) {
                    String label = labelFunction.apply(rs.getTimestamp(1));
                    Number value = rs.getObject(2) != null ? rs.getDouble(2) : null;
                    result.add(new NumberDatapoint(label, value));
                }
                return result.toArray(new NumberDatapoint[result.size()]);
            }
        }
    }));
}