iCal - all day events lasting one day are recognized for the day before

[DFS-90]

Hello,

I got a weird problem concerning the ICS calendar platform and all day events that last one single day. I managed to connect my Kopano calendar to HomeAssistant via SSL (Let’s encrypt).

Everything else works fine:

• umlauts
• all day events that last two or more days
• events that last a certain period of time, e. g. 8 am to 7 pm

As soon as I create an all day event that lasts one day, it is recognized for the day before.

Example:

• event is created for 2022/09/11, all day
• my mobile phone recognizes that event for 2022/09/11, all day
• Kopano WebApp recognizes that event for 2022/09/11, all day
• HomeAssistant recognizes that event for 2022/09/10, all day

This is an excerpt from the .ics-file:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//Kopano//11.0.2//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
BEGIN:VEVENT
TRANSP:TRANSPARENT
X-MICROSOFT-CDO-INTENDEDSTATUS:FREE
CREATED:20220907T230853Z
LAST-MODIFIED:20220907T230901Z
DTSTAMP:20220907T230901Z
DTSTART;VALUE=DATE:20220910
DTEND;VALUE=DATE:20220911
SUMMARY:Test WebApp ganztägig 11.09.
PRIORITY:5
CLASS:PUBLIC
UID:
040000008200E00074C5B7101A82E008000000008064F3D00EC3D801000000000000000001
000000C5EA678954DE4A9D840365DFBE14C377
X-MICROSOFT-CDO-OWNER-CRITICAL-CHANGE:20220908T075343Z
X-MICROSOFT-CDO-ATTENDEE-CRITICAL-CHANGE:20220908T075343Z
X-MICROSOFT-CDO-APPT-SEQUENCE:0
X-MICROSOFT-CDO-OWNERAPPTID:-1
X-MICROSOFT-CDO-ALLDAYEVENT:TRUE
END:VEVENT

I also did a second test with an empty calendar for 2022-09-12 to be sure:

BEGIN:VCALENDAR
VERSION:2.0
PRODID:-//Kopano//11.0.2//EN
CALSCALE:GREGORIAN
METHOD:PUBLISH
BEGIN:VEVENT
TRANSP:TRANSPARENT
X-MICROSOFT-CDO-INTENDEDSTATUS:FREE
CREATED:20220908T213511Z
LAST-MODIFIED:20220908T213511Z
DTSTAMP:20220908T213511Z
DTSTART;VALUE=DATE:20220911
DTEND;VALUE=DATE:20220912
SUMMARY:appointment test 2022-09-12, all day
PRIORITY:5
CLASS:PUBLIC
UID:
040000008200E00074C5B7101A82E00800000000800564E4CAC3D801000000000000000001
00000035A1B8102AFD4AE9AC9D0C575AFC4F57
X-MICROSOFT-CDO-OWNER-CRITICAL-CHANGE:20220908T213519Z
X-MICROSOFT-CDO-ATTENDEE-CRITICAL-CHANGE:20220908T213519Z
X-MICROSOFT-CDO-APPT-SEQUENCE:0
X-MICROSOFT-CDO-OWNERAPPTID:-1
X-MICROSOFT-CDO-ALLDAYEVENT:TRUE
END:VEVENT
END:VCALENDAR

The issue happened when trying to connect my Kopano calendar to HomeAssistant (please also see HomeAssistant community).

I’ve also tried to switch to kDAV instead of Kopano iCal - same behaviour.
All timezones are set correctly (“Europe/Berlin”).

Does anybody know how to troubleshoot this?

Thanks in advance,
David

[mithomas]

Looks a lot like a timezone-offset issue somewhere - this is probably not related to docker, but shares similarity with this issue about birthdays: https://jira.kopano.io/browse/KW-3203.

For birthdays dates are supposed to be stored as UTC timestamps in the database, however WebApp stores them in the local timezone which causes them to be off one hour (and therefore one day) via z-push. Maybe iCal and kDAV perform an adjustment they are not supposed to do, maybe the dates in the database have been written wrongly in the first place.

You have to find out what's actually supposed to be stored - likely only the Kopano dev team actually knows that so you have to reach out to them; you also want to check what the actual current values in your database are:

This data is in the properties table, stored in a PT_SYSTIME in FILETIME format (since this is what MAPI defines; here's an online converter for it: https://www.silisoftware.com/tools/date.php). It is split in the val_hi and val_lo fields which makes conversion to a readable value non straight-forward

This is what I use to directly query the database for birthdays:

select FROM_UNIXTIME((CONV(concat(LPAD(hex(val_hi),8,'0'),LPAD(hex(val_lo),8,'0')),16,10)-116444736000000000) DIV 10000000),val_hi,val_lo from properties where type = 64 and tag = 14914;

(Kudos to Paul Nijssen for the conversion code.)

Type 64 is birthdays (as per PidTagBirthday - you probably need PidTagStartDate or PidLidAppointmentStartWhole and the respective end values; tag 14914 means datatype PT_SYSTIME.

Another approach is to use the fairly easy python bindings; you could simply run:

docker run -it kopano/kopano_python /usr/bindocker run -it kopano/kopano_python /usr/bin/python3 -c "`cat your_script.py`"

where your script connects to your server instance and list the relevant appointment properties.

Once you know what is actually stored you could check might be able to find where the slippage happens.