Mysql, querying kopano / MAPI / PT_SYSTIME data

Question

We had some problems with a corrupted Kopano / Zarafa database and needed to query appointments. Because it took some time to figure out how to get the date time value.

This is the part that allows you to covert a type 64 (PT_systime) value to a datetime string:

FROM_UNIXTIME((CONV(concat(LPAD(hex(e.val_hi),8,'0'),LPAD(hex(e.val_lo),8,'0')),16,10)-116444736000000000) DIV 10000000)

The query to retrieve appointments

SELECT b.val_string as Location, c.val_string as Subject, d.val_string as Organisator, h.val_string as Attendee,
       FROM_UNIXTIME((CONV(concat(LPAD(hex(e.val_hi),8,'0'),LPAD(hex(e.val_lo),8,'0')),16,10)-116444736000000000) DIV 10000000) as start,
       FROM_UNIXTIME((CONV(concat(LPAD(hex(f.val_hi),8,'0'),LPAD(hex(f.val_lo),8,'0')),16,10)-116444736000000000) DIV 10000000) as stop,
       g.val_string as Tekst, a.hierarchyid  FROM properties a
                     LEFT JOIN properties b on b.hierarchyid = a.hierarchyid
                          AND b.tag = 32776
                     LEFT JOIN properties c on c.hierarchyid = a.hierarchyid
                          AND c.tag = 55
                     LEFT JOIN properties d on d.hierarchyid = a.hierarchyid
                          AND d.tag = 66
                    LEFT JOIN properties e on e.hierarchyid = a.hierarchyid
                          AND e.tag = 33206
                    LEFT JOIN properties f on f.hierarchyid = a.hierarchyid
                          AND f.tag = 33207
                    LEFT JOIN properties g on g.hierarchyid = a.hierarchyid
                          AND g.tag = 4096
                    LEFT JOIN properties h on (h.hierarchyid = a.hierarchyid
                          AND h.tag = 3588)

                     WHERE a.type=30 AND a.val_string = 'IPM.Appointment'
                     AND e.val_hi>30959050 order by start