commit 8325c344cbf6e16289f87bda99c472c873768518
parent 1bd614d44940f32250635f856f81d1bffd48c06d
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Fri, 15 Sep 2023 22:49:32 +0200
feat: use between for comparing dates
Diffstat:
| M | README.md | | | 41 | +++++++++++++++++++++++++++++++++++++++++ |
| M | server.go | | | 33 | ++++----------------------------- |
2 files changed, 45 insertions(+), 29 deletions(-)
diff --git a/README.md b/README.md
@@ -106,6 +106,47 @@ application work with as many remote servers as possible, all events from the
remote calendar are fetched during an update and the availability data is
stored in a local database for further processing.
+## Availability Query
+
+```
+// e ? ?
+// - | | false
+
+// s e ?
+// - + | false
+
+// s ? e ?
+// - | - | true (2)
+
+// s ? e
+// - | + true (2)
+
+// s ? ? e
+// - | | - true (3)
+
+// s e ?
+// + - | true (1)
+
+// s e
+// + + true (2)
+
+// s ? e
+// + | - true (1)
+
+// ? s e
+// | - + true (1)
+
+// ? s ? e
+// | - | - true (1)
+
+// ? s e
+// | + - false
+```
+
+TODO:
+- Sqlite date comparison always inclusive?
+- Picture for the query
+
## Development & Contributions
* All source code is available in this repository. Contributions are always
welcome!
diff --git a/server.go b/server.go
@@ -38,36 +38,11 @@ var create_availability_1 = `CREATE TABLE IF NOT EXISTS availability_1 (
end DATETIME NOT NULL,
recurring BOOL NOT NULL);`
-// e ? ?
-// - | | false
-// s e ?
-// - + | false
-// s ? e ?
-// - | - | true (2)
-// s ? e
-// - | + true (2)
-// s ? ? e
-// - | | - true (3)
-// s e ?
-// + - | true (1)
-// s e
-// + + true (2)
-// s ? e
-// + | - true (1)
-// ? s e
-// | - + true (1)
-// ? s ? e
-// | - | - true (1)
-// ? s e
-// | + - false
-
-
-// TODO: The sql query here does not seem to care about "inclusive" dates with
-// <= or >=, we have to tweak start/time by 1 min to make the edge cases 08:01,
-// 09:59, etc. behave correctly
+// BETWEEN is inclusive, we tweak start/time by 1 min to make the edge cases
+// 08:01, 09:59, etc. behave correctly
var available = `SELECT id FROM availability WHERE
- (start >= ? AND start < ?) OR
- (end > ? AND end <= ?) OR
+ (start BETWEEN ? AND ?) OR
+ (end BETWEEN ? AND ?) OR
(start <= ? AND end >= ?);`
func (s *Server) NewServer(c *config.Config) (*Server, error) {
