commit 1bd614d44940f32250635f856f81d1bffd48c06d
parent e8ba5865ce32e74b7381660d34fc3f5515dfdc22
Author: Andreas Gruhler <andreas.gruhler@adfinis.com>
Date: Fri, 15 Sep 2023 22:41:09 +0200
fix: timezone parsing
Diffstat:
| M | server.go | | | 180 | +++++++++++++++++++++++++++++++++++++++++++++++++++++++++---------------------- |
1 file changed, 130 insertions(+), 50 deletions(-)
diff --git a/server.go b/server.go
@@ -1,6 +1,7 @@
package main
import (
+ "regexp"
"time"
"strconv"
"github.com/teambition/rrule-go"
@@ -25,17 +26,17 @@ type Server struct {
}
var drop = `DROP TABLE IF EXISTS availability;
- DROP TABLE IF EXISTS availability_1;`
+ DROP TABLE IF EXISTS availability_1;`
var create_availability = `CREATE TABLE IF NOT EXISTS availability (
- id INTEGER NOT NULL PRIMARY KEY,
- start DATETIME NOT NULL,
- end DATETIME NOT NULL,
- tz TEXT NOT NULL);`
+ id INTEGER NOT NULL PRIMARY KEY,
+ start DATETIME NOT NULL,
+ end DATETIME NOT NULL,
+ recurring BOOL NOT NULL);`
var create_availability_1 = `CREATE TABLE IF NOT EXISTS availability_1 (
- id INTEGER NOT NULL PRIMARY KEY,
- start DATETIME NOT NULL,
- end DATETIME NOT NULL,
- tz TEXT NOT NULL);`
+ id INTEGER NOT NULL PRIMARY KEY,
+ start DATETIME NOT NULL,
+ end DATETIME NOT NULL,
+ recurring BOOL NOT NULL);`
// e ? ?
// - | | false
@@ -60,8 +61,11 @@ var create_availability_1 = `CREATE TABLE IF NOT EXISTS availability_1 (
// ? s e
// | + - false
-// TODO: Select only id
-var available = `SELECT * FROM availability WHERE
+
+// 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
+var available = `SELECT id FROM availability WHERE
(start >= ? AND start < ?) OR
(end > ? AND end <= ?) OR
(start <= ? AND end >= ?);`
@@ -119,12 +123,7 @@ func dayOfISOWeek(weekday int, week int, year int) time.Time {
}
func (s *Server) available(start string, end string) (bool, error) {
- var (
- id int
- rowStart time.Time
- rowEnd time.Time
- tz string
- )
+ var id int
stmt, err := s.db.Prepare(available)
@@ -133,18 +132,24 @@ func (s *Server) available(start string, end string) (bool, error) {
return false, err
}
- err = stmt.QueryRow(start, end, start, end, start, end).Scan(&id, &rowStart, &rowEnd, &tz)
+ err = stmt.QueryRow(start, end, start, end, start, end).Scan(&id)
if err != nil {
if err == sql.ErrNoRows {
return true, nil
}
- log.Printf("Different error: %v\n", err)
return false, err
}
return false, nil
}
func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Params) {
+ // TODO: make display timezone configurable
+ // display in local time
+ localLocation, err := time.LoadLocation("Local")
+ if err != nil {
+ return
+ }
+
userAgent := req.Header.Get("user-agent")
timeNow := time.Now()
@@ -152,7 +157,7 @@ func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Pa
year, week := timeNow.ISOWeek()
// overwrite with requested week
- week, err := strconv.Atoi(ps.ByName("week"))
+ week, err = strconv.Atoi(ps.ByName("week"))
if err != nil {
log.Printf("Cannot serve requested week '%s'")
w.WriteHeader(http.StatusBadRequest)
@@ -184,18 +189,22 @@ func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Pa
monTime:= dayOfISOWeek(1, week, year)
// TODO: use timeIt to go through the loops below, remove the static arrays
- timeIt := time.Date(monTime.Year(), monTime.Month(), monTime.Day(), 0, 0, 0, 0, time.UTC)
+ timeIt := time.Date(monTime.Year(), monTime.Month(), monTime.Day(), 0, 0, 0, 0, localLocation)
+ //dayEnd := time.Date(monTime.Year(), monTime.Month(), monTime.Day(), 17, 0, 0, 0, localLocation)
+ //weekEnd := dayEnd.Add(time.Hour * 24*5)
// Working hours - Eight to Five
+ //for h := 8; h < 17; h++ {
for _, h := range []int{8,9,10,11,12,13,14,15,16} {
var availability [5]string
// Working days - Monday through Friday
+ //for d := 0; d < 5; d++ {
for _, d := range []int{1,2,3,4,5} {
- start := timeIt.Add(time.Hour * time.Duration((d-1)*24 + h)).Format(sqlDateFmt)
- end := timeIt.Add(time.Hour * time.Duration((d-1)*24 + h+1)).Format(sqlDateFmt)
+ // add/remove 1 minute to make the sql time query in available() below behave correctly
+ start := timeIt.Add(time.Hour * time.Duration((d-1)*24 + h)).Add(1 * time.Minute).UTC().Format(sqlDateFmt)
+ end := timeIt.Add(time.Hour * time.Duration((d-1)*24 + h+1)).Add(-1 * time.Minute).UTC().Format(sqlDateFmt)
avi, err := s.available(start, end)
- log.Printf("Start: %s, End: %s\n", start, end)
- log.Printf("Avail: %v\n", avi)
+ log.Printf("Getting availability on day '%v' - '%v'\n", start, end)
if err != nil {
log.Printf("Error getting availability on day '%d' hour '%d'\n", d, h)
return
@@ -205,14 +214,15 @@ func (s *Server) Week(w http.ResponseWriter, req *http.Request, ps httprouter.Pa
} else {
availability[d-1] = "X"
}
+ //timeIt = timeIt.Add(time.Hour * 24)
}
- log.Printf("Avail Array: %v\n", availability)
pt.AddRow(fmt.Sprintf("%02d:00 - %02d:00", h, h+1),
availability[0],
availability[1],
availability[2],
availability[3],
availability[4])
+ //timeIt = timeIt.Add(time.Hour)
}
io.WriteString(w, fmt.Sprint(pt))
@@ -234,36 +244,71 @@ func (s *Server) Index(w http.ResponseWriter, req *http.Request, _ httprouter.Pa
func (s *Server) updateAvailability(calData []caldav.CalData) error {
// TODO: Make max for recurring events without end time configurable
- rrLimit := time.Date(2024, 12, 31, 0, 0, 0, 0, time.UTC)
+ rrLimit := time.Date(2024, 12, 31, 0, 0, 0, 0, time.Local)
+ //localNow := time.Now()
// Walk through events and store start/end time in the database
for _, event := range calData {
+ log.Printf("%v\n", event.Data)
dec := ical.NewDecoder(strings.NewReader(event.Data))
cal, err := dec.Decode()
+ var (
+ //tzProp *ical.Prop
+ // offset which is in use prior to this time zone observance
+ //tzOffsetTo string
+ //tzOffsetFrom string
+ // offset which is in use in this time zone observance
+ tzOffsetTo string
+ )
+
if err != nil {
return err
}
- for _, e := range cal.Events() {
- var tz string
- // https://github.com/emersion/go-ical/blob/master/enums.go
- if tzProp := e.Props.Get(ical.PropTimezoneID); tzProp != nil {
- tz = tzProp.Value
- } else {
- // TODO: make fallback timezone configurable
- tz = "Europe/Zurich"
+ // check for TIMEZONE component
+ for _, c := range cal.Children {
+ if c.Name == ical.CompTimezone {
+ for _, child := range c.Children {
+ //tzOffsetFrom = child.Props.Get(ical.PropTimezoneOffsetFrom).Value;
+ tzOffsetTo = child.Props.Get(ical.PropTimezoneOffsetTo).Value;
+ log.Println("tzOffsetTo:", tzOffsetTo)
+ }
}
+ }
+
+ var tzOffsetDuration time.Duration
+ re := regexp.MustCompile(`^([+-][0-9]{2}).*`)
+ matches := re.FindStringSubmatch(tzOffsetTo)
+ if len(matches) == 0 {
+ //_, offset := localNow.Zone()
+ //tzOffsetDuration = time.Second * time.Duration(offset)
+ //tzOffsetDuration = time.Second * time.Duration(-1 * offset)
+ //log.Printf("Cannot find time zone offset, using local offset: %v\n", tzOffsetDuration)
+ tzOffsetDuration = time.Duration(0)
+ log.Println("Cannot find time zone offset, using 0 offset.")
+ } else {
+ tzOffsetDurationInt, _ := strconv.Atoi(matches[1])
+ tzOffsetDuration = time.Hour * time.Duration(tzOffsetDurationInt)
+ log.Printf("Found time zone offset: %v\n", tzOffsetDuration)
+ // when the TIMEZONE comp is given, the DTSTART usually references the comp
+ // and we don't have to mess with the offset at all
+ //tzOffsetDuration = time.Duration(0)
+
+ // reset to UTC
+ tzOffsetDuration = time.Hour * time.Duration(-1 * tzOffsetDurationInt)
+ }
+ for _, e := range cal.Events() {
start := e.Props.Get(ical.PropDateTimeStart).Value
end := e.Props.Get(ical.PropDateTimeEnd).Value
// Parse all possible time formats
// Don't use ical DateTime(), has problems with some timezones
- startTime, err := parseTime(start, tz)
+ startTime, err := parseTime(start, tzOffsetDuration)
if err != nil {
return err
}
- endTime, err := parseTime(end, tz)
+ endTime, err := parseTime(end, tzOffsetDuration)
if err != nil {
return err
}
@@ -278,7 +323,7 @@ func (s *Server) updateAvailability(calData []caldav.CalData) error {
}
// set new availability in temporary table availability_1
- if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, ?);", startTime, endTime, tz); err != nil {
+ if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, false);", startTime, endTime); err != nil {
return err
}
@@ -296,7 +341,7 @@ func (s *Server) updateAvailability(calData []caldav.CalData) error {
// add exception dates to recurrence set exclusion list
exceptionDates := e.Props.Get(ical.PropExceptionDates)
if exceptionDates != nil {
- exDateTime, err := parseTime(exceptionDates.Value, tz)
+ exDateTime, err := parseTime(exceptionDates.Value, tzOffsetDuration)
if err != nil {
return err
}
@@ -306,7 +351,7 @@ func (s *Server) updateAvailability(calData []caldav.CalData) error {
// don't include the first event, handled outside the loop
rDates := set.Between(startTime, rrLimit, false)
for _, rrd := range rDates {
- if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, ?);", rrd, rrd.Add(duration), tz); err != nil {
+ if _, err = s.db.Exec("INSERT INTO availability_1 VALUES(NULL, ?, ?, true);", rrd, rrd.Add(duration)); err != nil {
return err
}
}
@@ -330,23 +375,58 @@ func (s *Server) updateAvailability(calData []caldav.CalData) error {
return nil
}
-func parseTime(timeStr string, tz string) (time.Time, error){
- local, err := time.LoadLocation(tz)
- if err != nil {
- return time.Time{}, err
- }
-
- t, err := time.ParseInLocation("20060102T150405Z", timeStr, local)
+// parse time from local time zone and return UTC time for sqlite db
+func parseTime(timeStr string, offset time.Duration) (time.Time, error){
+ //var (
+ // localTime *time.Location
+ // err error
+ //)
+
+ //re := regexp.MustCompile(`^(\(*([A-Za-z]+)|[A-Za-z/]+).*`)
+ //matches := re.FindStringSubmatch(tz)
+ //if len(matches) == 0 {
+ // log.Printf("Cannot time.LoadLocation() with empty TZID, reverting to UTC")
+ // // https://pkg.go.dev/time#LoadLocation
+ // // TODO: Would "Local" fallback be better here?
+ // // TODO: make fallback timezone for events from server configurable
+ // localTime, err = time.LoadLocation("Local")
+ //} else {
+ // log.Println("Found timezone: ", matches[0])
+ // localTime, err = time.LoadLocation(matches[0])
+ //}
+
+ //if err != nil {
+ // log.Printf("Cannot time.LoadLocation() with uknown TZID '%s', reverting to UTC", tz)
+ // // TODO: Would "Local" fallback be better here?
+ // // TODO: make fallback timezone for events from server configurable
+ // localTime, err = time.LoadLocation("Local")
+ // if err != nil {
+ // return time.Time{}, err
+ // }
+ //}
+
+ //t, err := time.ParseInLocation("20060102T150405Z", timeStr, localTime)
+ //if err != nil {
+ // t, err = time.ParseInLocation("20060102T150405", timeStr, localTime)
+ //}
+ //if err != nil {
+ // t, err = time.ParseInLocation("20060102", timeStr, localTime)
+ //}
+ //if err != nil {
+ // return time.Time{}, err
+ //}
+ //return t.UTC(), nil
+ t, err := time.Parse("20060102T150405Z", timeStr)
if err != nil {
- t, err = time.ParseInLocation("20060102T150405", timeStr, local)
+ t, err = time.Parse("20060102T150405", timeStr)
}
if err != nil {
- t, err = time.ParseInLocation("20060102", timeStr, local)
+ t, err = time.Parse("20060102", timeStr)
}
if err != nil {
return time.Time{}, err
}
- return t, nil
+ return t.Add(offset), nil
}
func (s *Server) showCubicles() {
