hourly-report.sql

WITH filtered_tag_log AS (
  SELECT
    data_value,
    logtime,
    logtime / (60 * 60 * 1000) log_hour
  FROM
    tag_log
  WHERE
    tag_id = 1
    AND
    logtime >= 1424257700000
    AND
    logtime < 1424478407000
), grouped_tag_log AS (
  SELECT DISTINCT ON (log_hour)
    log_hour,
    data_value last_data_value,
    TO_TIMESTAMP(logtime / 1000) last_record_time
  FROM
    filtered_tag_log
), hourly_diff AS (
  SELECT
    g_now.last_record_time now_last_record_time,
    date_trunc('hour', g_now.last_record_time) now_record_hour,
    g_now.last_data_value now_value,
    g_b4.last_record_time before_last_record_time,
    date_trunc('hour', g_b4.last_record_time) before_record_hour,
    g_b4.last_data_value before_value,
    ABS(g_now.last_data_value - g_b4.last_data_value) change
  FROM
    grouped_tag_log g_now,
    grouped_tag_log g_b4  
  WHERE
    g_now.log_hour - 1 = g_b4.log_hour
)

SELECT * FROM hourly_diff;