fix times for grafana

diff --git a/views.sql b/views.sql
index 7e542ca..808f76e 100644 (file)
--- a/views.sql
+++ b/views.sql
@@ -1,7 +1,7 @@
 -- 1 Temperature/Humidity
 drop view s_1;
 create view s_1 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -21,7 +21,7 @@ grant select on sm_1 to gread ;
 -- 2 2 Channel Push Notification
 drop view s_2;
 create view s_2 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -41,7 +41,7 @@ grant select on sm_2 to gread ;
 -- 29 Linear Displacement
 drop view s_29;
 create view s_29 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -61,7 +61,7 @@ grant select on sm_29 to gread ;
 -- 30 Structural Monitoring
 drop view s_30;
 create view s_30 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -81,7 +81,7 @@ grant select on sm_30 to gread ;
 -- 47 Wireless Tilt Sensor
 drop view s_47;
 create view s_47 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -101,7 +101,7 @@ grant select on sm_47 to gread ;
 -- 81 Two Channel Vibration Plus
 drop view s_81;
 create view s_81 as
-select time,
+select time AT TIME ZONE 'UTC-2' as time,
        addr,
        (json->'sensor_type')::int  as sensor_type,
        (json->'sensor_name')::text as sensor_name,
@@ -153,7 +153,8 @@ select time,
        (json->>'battery_percent')::float as battery_percent,
        (json->'counter')::int as counter
 from eg5120
-where sensor_type = 81;
+where sensor_type = 81
+order by time
 ;
 grant select on s_81 to gread ;
 drop table sm_81;