Mercurial > gemma

changeset 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 eb91ad1d7a93
children 1fa57ad05e1e
files pkg/controllers/gauges.go
diffstat 1 files changed, 10 insertions(+), 3 deletions(-) [+]
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(