mysql，查询kopano / mapi / pt_systime数据

Question

我们在损坏的Kopano / Zarafa数据库中遇到了一些问题，需要查询约会。因为花了一些时间来弄清楚如何获得日期时间值。

这是允许您掩盖DateTime字符串的64型（PT_Systime）值的部分：

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

检索约会的查询

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