-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathnetatmo_weather.sql
More file actions
174 lines (152 loc) · 5.72 KB
/
netatmo_weather.sql
File metadata and controls
174 lines (152 loc) · 5.72 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
/*
Source Server Type : PostgreSQL
Source Host : localhost:5432
Source Schema : netatmo_weather
Target Server Type : PostgreSQL
Date: 27/01/2023 09:17:53
*/
-- ----------------------------
-- Sequence structure for weather_time_utc_seq
-- ----------------------------
DROP SEQUENCE IF EXISTS "netatmo_weather"."weather_time_utc_seq";
CREATE SEQUENCE "netatmo_weather"."weather_time_utc_seq"
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
-- ----------------------------
-- Table structure for anemometer
-- ----------------------------
DROP TABLE IF EXISTS "netatmo_weather"."anemometer";
CREATE TABLE "netatmo_weather"."anemometer" (
"time_utc" int4 NOT NULL DEFAULT nextval('"netatmo_weather".weather_time_utc_seq'::regclass),
"windstrength" int2,
"windangle" int2,
"guststrength" int2,
"gustangle" int2,
"max_wind_str" int2,
"max_wind_angle" int2,
"date_max_wind_str" int4,
"timestamp" timestamp(6) DEFAULT now()
)
;
-- ----------------------------
-- Table structure for indoor
-- ----------------------------
DROP TABLE IF EXISTS "netatmo_weather"."indoor";
CREATE TABLE "netatmo_weather"."indoor" (
"time_utc" int4 NOT NULL,
"temperature" float4,
"co2" int4,
"humidity" int2,
"noise" int2,
"pressure" float4,
"absolutepressure" float4,
"min_temp" float4,
"max_temp" float4,
"date_max_temp" int4,
"date_min_temp" int4,
"temp_trend" varchar(20) COLLATE "pg_catalog"."default",
"pressure_trend" varchar(20) COLLATE "pg_catalog"."default",
"timestamp" timestamp(6) DEFAULT now()
)
;
-- ----------------------------
-- Table structure for outdoor
-- ----------------------------
DROP TABLE IF EXISTS "netatmo_weather"."outdoor";
CREATE TABLE "netatmo_weather"."outdoor" (
"time_utc" int4 NOT NULL DEFAULT nextval('"netatmo_weather".weather_time_utc_seq'::regclass),
"temperature" float4,
"humidity" int2,
"min_temp" float4,
"max_temp" float4,
"date_max_temp" int4,
"date_min_temp" int4,
"temp_trend" varchar(20) COLLATE "pg_catalog"."default",
"timestamp" timestamp(6) DEFAULT now()
)
;
-- ----------------------------
-- Table structure for rain
-- ----------------------------
DROP TABLE IF EXISTS "netatmo_weather"."rain";
CREATE TABLE "netatmo_weather"."rain" (
"time_utc" int4 NOT NULL DEFAULT nextval('"netatmo_weather".weather_time_utc_seq'::regclass),
"rain" float4,
"sum_rain_1" float4,
"sum_rain_24" float4,
"timestamp" timestamp(6) DEFAULT now()
)
;
-- ----------------------------
-- View structure for vw_indoor
-- ----------------------------
DROP VIEW IF EXISTS "netatmo_weather"."vw_indoor";
CREATE VIEW "netatmo_weather"."vw_indoor" AS SELECT to_timestamp(indoor.time_utc::double precision)::timestamp without time zone AS down_time,
indoor.temperature,
indoor.co2,
indoor.humidity,
indoor.noise,
indoor.pressure,
indoor.absolutepressure,
indoor.min_temp,
indoor.max_temp,
to_timestamp(indoor.date_max_temp::double precision)::timestamp without time zone AS date_max_temp,
to_timestamp(indoor.date_min_temp::double precision)::timestamp without time zone AS date_min_temp,
indoor.temp_trend,
indoor.pressure_trend,
indoor."timestamp" AS down_time_tmst
FROM netatmo_weather.indoor
ORDER BY indoor.time_utc DESC;
-- ----------------------------
-- View structure for vw_rain
-- ----------------------------
DROP VIEW IF EXISTS "netatmo_weather"."vw_rain";
CREATE VIEW "netatmo_weather"."vw_rain" AS SELECT to_timestamp(rain.time_utc::double precision)::timestamp without time zone AS down_time,
rain.rain,
rain.sum_rain_1,
rain.sum_rain_24
FROM netatmo_weather.rain
ORDER BY rain.time_utc DESC;
-- ----------------------------
-- View structure for vw_weather
-- ----------------------------
DROP VIEW IF EXISTS "netatmo_weather"."vw_weather";
CREATE VIEW "netatmo_weather"."vw_weather" AS SELECT to_timestamp(outdoor.time_utc::double precision)::timestamp without time zone AS down_time,
outdoor.temperature AS temperature,
outdoor.humidity AS humidity,
outdoor.min_temp,
outdoor.max_temp,
to_timestamp(outdoor.date_max_temp::double precision)::timestamp without time zone AS date_max_temp,
to_timestamp(outdoor.date_min_temp::double precision)::timestamp without time zone AS date_min_temp,
outdoor."timestamp" AS down_time_tmst
FROM netatmo_weather.outdoor
ORDER BY outdoor.time_utc DESC;
-- ----------------------------
-- Alter sequences owned by
-- ----------------------------
SELECT setval('"netatmo_weather"."weather_time_utc_seq"', 1, false);
-- ----------------------------
-- Primary Key structure for table anemometer
-- ----------------------------
ALTER TABLE "netatmo_weather"."anemometer" ADD CONSTRAINT "anemometer_pkey" PRIMARY KEY ("time_utc");
-- ----------------------------
-- Primary Key structure for table indoor
-- ----------------------------
ALTER TABLE "netatmo_weather"."indoor" ADD CONSTRAINT "indoor_pkey" PRIMARY KEY ("time_utc");
-- ----------------------------
-- Indexes structure for table outdoor
-- ----------------------------
CREATE UNIQUE INDEX "idx_weather_tm_utc_copy1" ON "netatmo_weather"."outdoor" USING btree (
"time_utc" "pg_catalog"."int4_ops" ASC NULLS LAST
);
-- ----------------------------
-- Primary Key structure for table outdoor
-- ----------------------------
ALTER TABLE "netatmo_weather"."outdoor" ADD CONSTRAINT "weather_copy1_pkey" PRIMARY KEY ("time_utc");
-- ----------------------------
-- Primary Key structure for table rain
-- ----------------------------
ALTER TABLE "netatmo_weather"."rain" ADD CONSTRAINT "rain_pkey" PRIMARY KEY ("time_utc");