66 lines
1.5 KiB
SQL
66 lines
1.5 KiB
SQL
-- create aqi table
|
|
CREATE TABLE aqi (
|
|
id SERIAL NOT NULL PRIMARY KEY,
|
|
epoch_time INT NOT NULL,
|
|
sensor_id SMALLINT NOT NULL,
|
|
time_stamp VARCHAR(20) NOT NULL,
|
|
uptime INT NOT NULL,
|
|
pm25 FLOAT4 NOT NULL,
|
|
pm10 FLOAT4 NOT NULL,
|
|
aqi_value FLOAT4 NOT NULL,
|
|
aqi_category VARCHAR(40) NOT NULL
|
|
);
|
|
|
|
-- index for epoch_time where queries
|
|
CREATE INDEX aqi_epochtime ON aqi (epoch_time DESC);
|
|
|
|
-- example aqi insert
|
|
INSERT INTO aqi (
|
|
epoch_time,
|
|
sensor_id,
|
|
time_stamp,
|
|
uptime,
|
|
pm25,
|
|
pm10,
|
|
aqi_value,
|
|
aqi_category
|
|
) VALUES (
|
|
1613648178, 1, '2021-02-18 18:36:18', 206728, 20.4, 22.8, 67.0, 'Moderate'
|
|
);
|
|
|
|
|
|
-- create weather table
|
|
CREATE TABLE weather (
|
|
id SERIAL NOT NULL PRIMARY KEY,
|
|
epoch_time INT NOT NULL,
|
|
sensor_id SMALLINT NOT NULL,
|
|
time_stamp VARCHAR(20) NOT NULL,
|
|
temperature FLOAT4 NOT NULL,
|
|
pressure FLOAT4 NOT NULL,
|
|
humidity FLOAT4 NOT NULL,
|
|
wind_speed FLOAT4 NOT NULL,
|
|
wind_direction INT2 NOT NULL,
|
|
weather_name VARCHAR(29) NOT NULL,
|
|
weather_icon VARCHAR(3) NOT NULL
|
|
);
|
|
|
|
-- index for epoch_time where queries
|
|
CREATE INDEX weather_epochtime ON weather (epoch_time DESC);
|
|
|
|
-- example weather insert
|
|
INSERT INTO weather (
|
|
epoch_time,
|
|
sensor_id,
|
|
time_stamp,
|
|
temperature,
|
|
pressure,
|
|
humidity,
|
|
wind_speed,
|
|
wind_direction,
|
|
weather_name,
|
|
weather_icon
|
|
) VALUES (
|
|
1613648178, 1, '2021-02-18 18:36:18', 27.32, 982.41,
|
|
39.62598, 3.09, 40, 'Clouds', '04n'
|
|
);
|