3 select time AT TIME ZONE 'UTC-2' as time,
5 (json->'sensor_type')::int as sensor_type,
6 (json->'sensor_name')::text as sensor_name,
7 (json->>'battery')::float as bettery,
8 (json->>'battery_percent')::float as battery_percent,
9 (json->'counter')::int as counter
12 grant select on s_all to gread ;
14 -- 1 Temperature/Humidity
17 select time AT TIME ZONE 'UTC-2' as time,
19 (json->'sensor_type')::int as sensor_type,
20 (json->'sensor_name')::text as sensor_name,
21 (json->'sensor_data'->'temperature')::float as temperature,
22 (json->'sensor_data'->'humidity')::float as humidity,
23 (json->>'battery')::float as bettery,
24 (json->>'battery_percent')::float as battery_percent,
25 (json->'counter')::int as counter
27 where sensor_type = 1;
29 grant select on s_1 to gread ;
31 -- 2 2 Channel Push Notification
34 select time AT TIME ZONE 'UTC-2' as time,
36 (json->'sensor_type')::int as sensor_type,
37 (json->'sensor_name')::text as sensor_name,
38 (json->'sensor_data'->'input_1')::int as input_1,
39 (json->'sensor_data'->'input_2')::int as input_2,
40 (json->>'battery')::float as bettery,
41 (json->>'battery_percent')::float as battery_percent,
42 (json->'counter')::int as counter
44 where sensor_type = 2;
46 grant select on s_2 to gread ;
48 -- 29 Linear Displacement
51 select time AT TIME ZONE 'UTC-2' as time,
53 (json->'sensor_type')::int as sensor_type,
54 (json->'sensor_name')::text as sensor_name,
55 (json->'sensor_data'->'adc')::int as adc,
56 (json->'sensor_data'->'position')::float as position,
57 (json->>'battery')::float as bettery,
58 (json->>'battery_percent')::float as battery_percent,
59 (json->'counter')::int as counter
61 where sensor_type = 29;
63 grant select on s_29 to gread ;
65 -- 30 Structural Monitoring
68 select time AT TIME ZONE 'UTC-2' as time,
70 (json->'sensor_type')::int as sensor_type,
71 (json->'sensor_name')::text as sensor_name,
72 (json->'sensor_data'->'adc')::int as adc,
73 (json->'sensor_data'->'position')::float as position,
74 (json->>'battery')::float as bettery,
75 (json->>'battery_percent')::float as battery_percent,
76 (json->'counter')::int as counter
78 where sensor_type = 30;
80 grant select on s_30 to gread ;
82 -- 47 Wireless Tilt Sensor
85 select time AT TIME ZONE 'UTC-2' as time,
87 (json->'sensor_type')::int as sensor_type,
88 (json->'sensor_name')::text as sensor_name,
89 (json->'sensor_data'->'Roll')::float as Roll,
90 (json->'sensor_data'->'Pitch')::float as Pitch,
91 (json->>'battery')::float as bettery,
92 (json->>'battery_percent')::float as battery_percent,
93 (json->'counter')::int as counter
95 where sensor_type = 47;
97 grant select on s_47 to gread ;
99 -- 81 Two Channel Vibration Plus
102 select time AT TIME ZONE 'UTC-2' as time,
104 (json->'sensor_type')::int as sensor_type,
105 (json->'sensor_name')::text as sensor_name,
106 (json->'sensor_data'->'s1_temperature')::float as s1_temperature,
107 (json->'sensor_data'->'s2_temperature')::float as s2_temperature,
108 (json->'sensor_data'->'x1_max_ACC_G')::float as x1_max_ACC_G,
109 (json->'sensor_data'->'x1_rms_ACC_G')::float as x1_rms_ACC_G,
110 (json->'sensor_data'->'x2_max_ACC_G')::float as x2_max_ACC_G,
111 (json->'sensor_data'->'x2_rms_ACC_G')::float as x2_rms_ACC_G,
112 (json->'sensor_data'->'y1_max_ACC_G')::float as y1_max_ACC_G,
113 (json->'sensor_data'->'y1_rms_ACC_G')::float as y1_rms_ACC_G,
114 (json->'sensor_data'->'y2_max_ACC_G')::float as y2_max_ACC_G,
115 (json->'sensor_data'->'y2_rms_ACC_G')::float as y2_rms_ACC_G,
116 (json->'sensor_data'->'z1_max_ACC_G')::float as z1_max_ACC_G,
117 (json->'sensor_data'->'z1_rms_ACC_G')::float as z1_rms_ACC_G,
118 (json->'sensor_data'->'z2_max_ACC_G')::float as z2_max_ACC_G,
119 (json->'sensor_data'->'z2_rms_ACC_G')::float as z2_rms_ACC_G,
120 (json->'sensor_data'->'x1_peak_one_Hz')::float as x1_peak_one_Hz,
121 (json->'sensor_data'->'x1_peak_two_Hz')::float as x1_peak_two_Hz,
122 (json->'sensor_data'->'x2_peak_one_Hz')::float as x2_peak_one_Hz,
123 (json->'sensor_data'->'x2_peak_two_Hz')::float as x2_peak_two_Hz,
124 (json->'sensor_data'->'y1_peak_one_Hz')::float as y1_peak_one_Hz,
125 (json->'sensor_data'->'y1_peak_two_Hz')::float as y1_peak_two_Hz,
126 (json->'sensor_data'->'y2_peak_one_Hz')::float as y2_peak_one_Hz,
127 (json->'sensor_data'->'y2_peak_two_Hz')::float as y2_peak_two_Hz,
128 (json->'sensor_data'->'z1_peak_one_Hz')::float as z1_peak_one_Hz,
129 (json->'sensor_data'->'z1_peak_two_Hz')::float as z1_peak_two_Hz,
130 (json->'sensor_data'->'z2_peak_one_Hz')::float as z2_peak_one_Hz,
131 (json->'sensor_data'->'z2_peak_two_Hz')::float as z2_peak_two_Hz,
132 (json->'sensor_data'->'x1_peak_three_Hz')::float as x1_peak_three_Hz,
133 (json->'sensor_data'->'x2_peak_three_Hz')::float as x2_peak_three_Hz,
134 (json->'sensor_data'->'y1_peak_three_Hz')::float as y1_peak_three_Hz,
135 (json->'sensor_data'->'y2_peak_three_Hz')::float as y2_peak_three_Hz,
136 (json->'sensor_data'->'z1_peak_three_Hz')::float as z1_peak_three_Hz,
137 (json->'sensor_data'->'z2_peak_three_Hz')::float as z2_peak_three_Hz,
138 (json->'sensor_data'->'x1_displacement_mm')::float as x1_displacement_mm,
139 (json->'sensor_data'->'x1_velocity_mm_sec')::float as x1_velocity_mm_sec,
140 (json->'sensor_data'->'x2_displacement_mm')::float as x2_displacement_mm,
141 (json->'sensor_data'->'x2_velocity_mm_sec')::float as x2_velocity_mm_sec,
142 (json->'sensor_data'->'y1_displacement_mm')::float as y1_displacement_mm,
143 (json->'sensor_data'->'y1_velocity_mm_sec')::float as y1_velocity_mm_sec,
144 (json->'sensor_data'->'y2_displacement_mm')::float as y2_displacement_mm,
145 (json->'sensor_data'->'y2_velocity_mm_sec')::float as y2_velocity_mm_sec,
146 (json->'sensor_data'->'z1_displacement_mm')::float as z1_displacement_mm,
147 (json->'sensor_data'->'z1_velocity_mm_sec')::float as z1_velocity_mm_sec,
148 (json->'sensor_data'->'z2_displacement_mm')::float as z2_displacement_mm,
149 (json->'sensor_data'->'z2_velocity_mm_sec')::float as z2_velocity_mm_sec,
150 (json->>'battery')::float as bettery,
151 (json->>'battery_percent')::float as battery_percent,
152 (json->'counter')::int as counter
154 where sensor_type = 81
157 grant select on s_81 to gread ;