Mercurial > gemma
diff pkg/controllers/gauges.go @ 2806:c6374c520228
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 |
line wrap: on
line diff
--- a/pkg/controllers/gauges.go Mon Mar 25 21:26:52 2019 +0100 +++ b/pkg/controllers/gauges.go Tue Mar 26 07:41:53 2019 +0100 @@ -96,7 +96,7 @@ $4::char(5), $5::int ) - AND extract(year from measure_date) = $6 + AND measure_date BETWEEN $6 AND $7 ORDER BY measure_date ` @@ -151,12 +151,18 @@ return } - year, _ := strconv.ParseInt(mux.Vars(req)["year"], 10, 64) + year, _ := strconv.Atoi(mux.Vars(req)["year"]) conn := middleware.GetDBConn(req) ctx := req.Context() + begin := time.Date(year, time.January, 1, 0, 0, 0, 0, time.UTC) + end := time.Date(year+1, time.January, 1, 0, 0, 0, 0, time.UTC).Add(-time.Microsecond) + + log.Printf("info: begin %s\n", begin) + log.Printf("info: end %s\n", end) + rows, err := conn.QueryContext( ctx, selectYearWaterlevelsMeasuredSQL, @@ -165,7 +171,8 @@ isrs.FairwaySection, isrs.Orc, isrs.Hectometre, - year, + begin, + end, ) if err != nil { http.Error(