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 create table sm_1 as select * from s_1;
19 grant select on sm_1 to gread ;
21 -- 2 2 Channel Push Notification
24 select time AT TIME ZONE 'UTC-2' as time,
26 (json->'sensor_type')::int as sensor_type,
27 (json->'sensor_name')::text as sensor_name,
28 (json->'sensor_data'->'input_1')::int as input_1,
29 (json->'sensor_data'->'input_2')::int as input_2,
30 (json->>'battery')::float as bettery,
31 (json->>'battery_percent')::float as battery_percent,
32 (json->'counter')::int as counter
34 where sensor_type = 2;
36 grant select on s_2 to gread ;
38 create table sm_2 as select * from s_2;
39 grant select on sm_2 to gread ;
41 -- 29 Linear Displacement
44 select time AT TIME ZONE 'UTC-2' as time,
46 (json->'sensor_type')::int as sensor_type,
47 (json->'sensor_name')::text as sensor_name,
48 (json->'sensor_data'->'adc')::int as adc,
49 (json->'sensor_data'->'position')::float as position,
50 (json->>'battery')::float as bettery,
51 (json->>'battery_percent')::float as battery_percent,
52 (json->'counter')::int as counter
54 where sensor_type = 29;
56 grant select on s_29 to gread ;
58 create table sm_29 as select * from s_29;
59 grant select on sm_29 to gread ;
61 -- 30 Structural Monitoring
64 select time AT TIME ZONE 'UTC-2' as time,
66 (json->'sensor_type')::int as sensor_type,
67 (json->'sensor_name')::text as sensor_name,
68 (json->'sensor_data'->'adc')::int as adc,
69 (json->'sensor_data'->'position')::float as position,
70 (json->>'battery')::float as bettery,
71 (json->>'battery_percent')::float as battery_percent,
72 (json->'counter')::int as counter
74 where sensor_type = 30;
76 grant select on s_30 to gread ;
78 create table sm_30 as select * from s_30;
79 grant select on sm_30 to gread ;
81 -- 47 Wireless Tilt Sensor
84 select time AT TIME ZONE 'UTC-2' as time,
86 (json->'sensor_type')::int as sensor_type,
87 (json->'sensor_name')::text as sensor_name,
88 (json->'sensor_data'->'Roll')::float as Roll,
89 (json->'sensor_data'->'Pitch')::float as Pitch,
90 (json->>'battery')::float as bettery,
91 (json->>'battery_percent')::float as battery_percent,
92 (json->'counter')::int as counter
94 where sensor_type = 47;
96 grant select on s_47 to gread ;
98 create table sm_47 as select * from s_47;
99 grant select on sm_47 to gread ;
101 -- 81 Two Channel Vibration Plus
104 select time AT TIME ZONE 'UTC-2' as time,
106 (json->'sensor_type')::int as sensor_type,
107 (json->'sensor_name')::text as sensor_name,
108 (json->'sensor_data'->'s1_temperature')::float as s1_temperature,
109 (json->'sensor_data'->'s2_temperature')::float as s2_temperature,
110 (json->'sensor_data'->'x1_max_ACC_G')::float as x1_max_ACC_G,
111 (json->'sensor_data'->'x1_rms_ACC_G')::float as x1_rms_ACC_G,
112 (json->'sensor_data'->'x2_max_ACC_G')::float as x2_max_ACC_G,
113 (json->'sensor_data'->'x2_rms_ACC_G')::float as x2_rms_ACC_G,
114 (json->'sensor_data'->'y1_max_ACC_G')::float as y1_max_ACC_G,
115 (json->'sensor_data'->'y1_rms_ACC_G')::float as y1_rms_ACC_G,
116 (json->'sensor_data'->'y2_max_ACC_G')::float as y2_max_ACC_G,
117 (json->'sensor_data'->'y2_rms_ACC_G')::float as y2_rms_ACC_G,
118 (json->'sensor_data'->'z1_max_ACC_G')::float as z1_max_ACC_G,
119 (json->'sensor_data'->'z1_rms_ACC_G')::float as z1_rms_ACC_G,
120 (json->'sensor_data'->'z2_max_ACC_G')::float as z2_max_ACC_G,
121 (json->'sensor_data'->'z2_rms_ACC_G')::float as z2_rms_ACC_G,
122 (json->'sensor_data'->'x1_peak_one_Hz')::float as x1_peak_one_Hz,
123 (json->'sensor_data'->'x1_peak_two_Hz')::float as x1_peak_two_Hz,
124 (json->'sensor_data'->'x2_peak_one_Hz')::float as x2_peak_one_Hz,
125 (json->'sensor_data'->'x2_peak_two_Hz')::float as x2_peak_two_Hz,
126 (json->'sensor_data'->'y1_peak_one_Hz')::float as y1_peak_one_Hz,
127 (json->'sensor_data'->'y1_peak_two_Hz')::float as y1_peak_two_Hz,
128 (json->'sensor_data'->'y2_peak_one_Hz')::float as y2_peak_one_Hz,
129 (json->'sensor_data'->'y2_peak_two_Hz')::float as y2_peak_two_Hz,
130 (json->'sensor_data'->'z1_peak_one_Hz')::float as z1_peak_one_Hz,
131 (json->'sensor_data'->'z1_peak_two_Hz')::float as z1_peak_two_Hz,
132 (json->'sensor_data'->'z2_peak_one_Hz')::float as z2_peak_one_Hz,
133 (json->'sensor_data'->'z2_peak_two_Hz')::float as z2_peak_two_Hz,
134 (json->'sensor_data'->'x1_peak_three_Hz')::float as x1_peak_three_Hz,
135 (json->'sensor_data'->'x2_peak_three_Hz')::float as x2_peak_three_Hz,
136 (json->'sensor_data'->'y1_peak_three_Hz')::float as y1_peak_three_Hz,
137 (json->'sensor_data'->'y2_peak_three_Hz')::float as y2_peak_three_Hz,
138 (json->'sensor_data'->'z1_peak_three_Hz')::float as z1_peak_three_Hz,
139 (json->'sensor_data'->'z2_peak_three_Hz')::float as z2_peak_three_Hz,
140 (json->'sensor_data'->'x1_displacement_mm')::float as x1_displacement_mm,
141 (json->'sensor_data'->'x1_velocity_mm_sec')::float as x1_velocity_mm_sec,
142 (json->'sensor_data'->'x2_displacement_mm')::float as x2_displacement_mm,
143 (json->'sensor_data'->'x2_velocity_mm_sec')::float as x2_velocity_mm_sec,
144 (json->'sensor_data'->'y1_displacement_mm')::float as y1_displacement_mm,
145 (json->'sensor_data'->'y1_velocity_mm_sec')::float as y1_velocity_mm_sec,
146 (json->'sensor_data'->'y2_displacement_mm')::float as y2_displacement_mm,
147 (json->'sensor_data'->'y2_velocity_mm_sec')::float as y2_velocity_mm_sec,
148 (json->'sensor_data'->'z1_displacement_mm')::float as z1_displacement_mm,
149 (json->'sensor_data'->'z1_velocity_mm_sec')::float as z1_velocity_mm_sec,
150 (json->'sensor_data'->'z2_displacement_mm')::float as z2_displacement_mm,
151 (json->'sensor_data'->'z2_velocity_mm_sec')::float as z2_velocity_mm_sec,
152 (json->>'battery')::float as bettery,
153 (json->>'battery_percent')::float as battery_percent,
154 (json->'counter')::int as counter
156 where sensor_type = 81
159 grant select on s_81 to gread ;
161 create table sm_81 as select * from s_81;
162 grant select on sm_81 to gread ;