Mercurial > gemma

comparison pkg/controllers/gauges.go @ 2806:c6374c520228

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use between filter to speed up SQL for fetching yearly waterlevels.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Mar 2019 07:41:53 +0100
parents 46a9a7c1281f
children 1fa57ad05e1e
comparison
equal deleted inserted replaced
2805:eb91ad1d7a93 2806:c6374c520228
94 $2::char(3), 94 $2::char(3),
95 $3::char(5), 95 $3::char(5),
96 $4::char(5), 96 $4::char(5),
97 $5::int 97 $5::int
98 ) 98 )
99 AND extract(year from measure_date) = $6 99 AND measure_date BETWEEN $6 AND $7
100 ORDER BY measure_date 100 ORDER BY measure_date
101 ` 101 `
102 102
103 selectWaterlevelsMeasuredSQL = ` 103 selectWaterlevelsMeasuredSQL = `
104 SELECT 104 SELECT
149 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err), 149 rw, fmt.Sprintf("error: Invalid ISRS code: %v", err),
150 http.StatusBadRequest) 150 http.StatusBadRequest)
151 return 151 return
152 } 152 }
153 153
154 year, _ := strconv.ParseInt(mux.Vars(req)["year"], 10, 64) 154 year, _ := strconv.Atoi(mux.Vars(req)["year"])
155 155
156 conn := middleware.GetDBConn(req) 156 conn := middleware.GetDBConn(req)
157 157
158 ctx := req.Context() 158 ctx := req.Context()
159
160 begin := time.Date(year, time.January, 1, 0, 0, 0, 0, time.UTC)
161 end := time.Date(year+1, time.January, 1, 0, 0, 0, 0, time.UTC).Add(-time.Microsecond)
162
163 log.Printf("info: begin %s\n", begin)
164 log.Printf("info: end %s\n", end)
159 165
160 rows, err := conn.QueryContext( 166 rows, err := conn.QueryContext(
161 ctx, 167 ctx,
162 selectYearWaterlevelsMeasuredSQL, 168 selectYearWaterlevelsMeasuredSQL,
163 isrs.CountryCode, 169 isrs.CountryCode,
164 isrs.LoCode, 170 isrs.LoCode,
165 isrs.FairwaySection, 171 isrs.FairwaySection,
166 isrs.Orc, 172 isrs.Orc,
167 isrs.Hectometre, 173 isrs.Hectometre,
168 year, 174 begin,
175 end,
169 ) 176 )
170 if err != nil { 177 if err != nil {
171 http.Error( 178 http.Error(
172 rw, fmt.Sprintf("error: %v", err), 179 rw, fmt.Sprintf("error: %v", err),
173 http.StatusInternalServerError) 180 http.StatusInternalServerError)