1 -- 1 Temperature/Humidity
4 select time AT TIME ZONE 'UTC-2' as time,
6 (json->'sensor_type')::int as sensor_type,
7 (json->'sensor_name')::text as sensor_name,
8 (json->'sensor_data'->'temperature')::float as temperature,
9 (json->'sensor_data'->'humidity')::float as humidity,
10 (json->>'battery')::float as bettery,
11 (json->>'battery_percent')::float as battery_percent,
12 (json->'counter')::int as counter
14 where sensor_type = 1;
16 grant select on s_1 to gread ;
18 -- 2 2 Channel Push Notification
21 select time AT TIME ZONE 'UTC-2' as time,
23 (json->'sensor_type')::int as sensor_type,
24 (json->'sensor_name')::text as sensor_name,
25 (json->'sensor_data'->'input_1')::int as input_1,
26 (json->'sensor_data'->'input_2')::int as input_2,
27 (json->>'battery')::float as bettery,
28 (json->>'battery_percent')::float as battery_percent,
29 (json->'counter')::int as counter
31 where sensor_type = 2;
33 grant select on s_2 to gread ;
35 -- 29 Linear Displacement
38 select time AT TIME ZONE 'UTC-2' as time,
40 (json->'sensor_type')::int as sensor_type,
41 (json->'sensor_name')::text as sensor_name,
42 (json->'sensor_data'->'adc')::int as adc,
43 (json->'sensor_data'->'position')::float as position,
44 (json->>'battery')::float as bettery,
45 (json->>'battery_percent')::float as battery_percent,
46 (json->'counter')::int as counter
48 where sensor_type = 29;
50 grant select on s_29 to gread ;
52 -- 30 Structural Monitoring
55 select time AT TIME ZONE 'UTC-2' as time,
57 (json->'sensor_type')::int as sensor_type,
58 (json->'sensor_name')::text as sensor_name,
59 (json->'sensor_data'->'adc')::int as adc,
60 (json->'sensor_data'->'position')::float as position,
61 (json->>'battery')::float as bettery,
62 (json->>'battery_percent')::float as battery_percent,
63 (json->'counter')::int as counter
65 where sensor_type = 30;
67 grant select on s_30 to gread ;
69 -- 47 Wireless Tilt Sensor
72 select time AT TIME ZONE 'UTC-2' as time,
74 (json->'sensor_type')::int as sensor_type,
75 (json->'sensor_name')::text as sensor_name,
76 (json->'sensor_data'->'Roll')::float as Roll,
77 (json->'sensor_data'->'Pitch')::float as Pitch,
78 (json->>'battery')::float as bettery,
79 (json->>'battery_percent')::float as battery_percent,
80 (json->'counter')::int as counter
82 where sensor_type = 47;
84 grant select on s_47 to gread ;
86 -- 81 Two Channel Vibration Plus
89 select time AT TIME ZONE 'UTC-2' as time,
91 (json->'sensor_type')::int as sensor_type,
92 (json->'sensor_name')::text as sensor_name,
93 (json->'sensor_data'->'s1_temperature')::float as s1_temperature,
94 (json->'sensor_data'->'s2_temperature')::float as s2_temperature,
95 (json->'sensor_data'->'x1_max_ACC_G')::float as x1_max_ACC_G,
96 (json->'sensor_data'->'x1_rms_ACC_G')::float as x1_rms_ACC_G,
97 (json->'sensor_data'->'x2_max_ACC_G')::float as x2_max_ACC_G,
98 (json->'sensor_data'->'x2_rms_ACC_G')::float as x2_rms_ACC_G,
99 (json->'sensor_data'->'y1_max_ACC_G')::float as y1_max_ACC_G,
100 (json->'sensor_data'->'y1_rms_ACC_G')::float as y1_rms_ACC_G,
101 (json->'sensor_data'->'y2_max_ACC_G')::float as y2_max_ACC_G,
102 (json->'sensor_data'->'y2_rms_ACC_G')::float as y2_rms_ACC_G,
103 (json->'sensor_data'->'z1_max_ACC_G')::float as z1_max_ACC_G,
104 (json->'sensor_data'->'z1_rms_ACC_G')::float as z1_rms_ACC_G,
105 (json->'sensor_data'->'z2_max_ACC_G')::float as z2_max_ACC_G,
106 (json->'sensor_data'->'z2_rms_ACC_G')::float as z2_rms_ACC_G,
107 (json->'sensor_data'->'x1_peak_one_Hz')::float as x1_peak_one_Hz,
108 (json->'sensor_data'->'x1_peak_two_Hz')::float as x1_peak_two_Hz,
109 (json->'sensor_data'->'x2_peak_one_Hz')::float as x2_peak_one_Hz,
110 (json->'sensor_data'->'x2_peak_two_Hz')::float as x2_peak_two_Hz,
111 (json->'sensor_data'->'y1_peak_one_Hz')::float as y1_peak_one_Hz,
112 (json->'sensor_data'->'y1_peak_two_Hz')::float as y1_peak_two_Hz,
113 (json->'sensor_data'->'y2_peak_one_Hz')::float as y2_peak_one_Hz,
114 (json->'sensor_data'->'y2_peak_two_Hz')::float as y2_peak_two_Hz,
115 (json->'sensor_data'->'z1_peak_one_Hz')::float as z1_peak_one_Hz,
116 (json->'sensor_data'->'z1_peak_two_Hz')::float as z1_peak_two_Hz,
117 (json->'sensor_data'->'z2_peak_one_Hz')::float as z2_peak_one_Hz,
118 (json->'sensor_data'->'z2_peak_two_Hz')::float as z2_peak_two_Hz,
119 (json->'sensor_data'->'x1_peak_three_Hz')::float as x1_peak_three_Hz,
120 (json->'sensor_data'->'x2_peak_three_Hz')::float as x2_peak_three_Hz,
121 (json->'sensor_data'->'y1_peak_three_Hz')::float as y1_peak_three_Hz,
122 (json->'sensor_data'->'y2_peak_three_Hz')::float as y2_peak_three_Hz,
123 (json->'sensor_data'->'z1_peak_three_Hz')::float as z1_peak_three_Hz,
124 (json->'sensor_data'->'z2_peak_three_Hz')::float as z2_peak_three_Hz,
125 (json->'sensor_data'->'x1_displacement_mm')::float as x1_displacement_mm,
126 (json->'sensor_data'->'x1_velocity_mm_sec')::float as x1_velocity_mm_sec,
127 (json->'sensor_data'->'x2_displacement_mm')::float as x2_displacement_mm,
128 (json->'sensor_data'->'x2_velocity_mm_sec')::float as x2_velocity_mm_sec,
129 (json->'sensor_data'->'y1_displacement_mm')::float as y1_displacement_mm,
130 (json->'sensor_data'->'y1_velocity_mm_sec')::float as y1_velocity_mm_sec,
131 (json->'sensor_data'->'y2_displacement_mm')::float as y2_displacement_mm,
132 (json->'sensor_data'->'y2_velocity_mm_sec')::float as y2_velocity_mm_sec,
133 (json->'sensor_data'->'z1_displacement_mm')::float as z1_displacement_mm,
134 (json->'sensor_data'->'z1_velocity_mm_sec')::float as z1_velocity_mm_sec,
135 (json->'sensor_data'->'z2_displacement_mm')::float as z2_displacement_mm,
136 (json->'sensor_data'->'z2_velocity_mm_sec')::float as z2_velocity_mm_sec,
137 (json->>'battery')::float as bettery,
138 (json->>'battery_percent')::float as battery_percent,
139 (json->'counter')::int as counter
141 where sensor_type = 81
144 grant select on s_81 to gread ;