mRFC 0020: Midgard Date and Time Handling

This document outlines how the date and time information should be managed with Midgard 2. This mRFC been submitted to the Midgard Community for discussion and approval under the Creative Commons Attribution-ShareAlike license.

The reason for posting this mRFC is to settle the debate of different date handling options on the different layers of the Midgard API.

Current status

Midgard stores time formats in at least three different formats

SQL datetimes
Straight, unprocessed Unix timestamps
DD.MM.YYYY (person birthday field)

Differences in time information

Midgard needs to deal with two types of timestamps:

"Current", like article publication date that is easily handled as a timestamp
"Historical", like person's birthdate that can't be handled as timestamp due to epoch starting in 1970

However, using two different time formats at random is not good. So, to unify them we need to figure out a good common format. Most programming languages nowadays seem to have good date types for handling the problems, but unfortunately PHP is not one of these.

Proposal for unification

The idea would be to unify all date and time handling under a common "datetime" type on MgdSchema level. The different stacks of the Midgard environment would handle the datetimes in the following way:

In MgdSchemas the date/time fields should be defined as type="datetime"
SQL datetime type should be used for all storage
Repligard should use ISO 8601 formatted date strings
MgdSchema should abstract the datetimes to a combined gdate/gtime type using libtai
midgard-java should abstract the gdate/gtimes to Java Date objects
midgard-php should handle the datetimes as ISO 8601 formatted date strings
MidCOM DBA should handle datetimes using the PEAR Date library

Example: Query Builder in MidCOM

MidCOM abstracts the Midgard Query Builder into its Database Abstraction layer. With the Midgard Query Builder the query constraints should be handled in the following way for the different stacks involved:

Date query constraints are entered into MidCOM DBA Query Builder as PEAR Date objects
MidCOM DBA layer converts the PEAR Dates into ISO 8601 strings for midgard-php
Query Builder in Midgard core converts the ISO 8601 strings into SQL date queries

Exception: timestamps in Core types

Usage of timestamps instead of real datetimes is justified for some types defined by Midgard Core for performance reasons. For these there should be a separate type="timestamp" in Midgard Schemas.

Timestamps should be speficially limited to properties which may only have a date value ranging somewhere in the running time of Midgard (i.e. inside Unix epoch). Examples would be:

Object metadata: approved
Object metadata: locked

This exception is left here until performance tests of the full datetime implementation have been done. If datetime performance is not seen as limiting compared to timestamps, then usage of timestamps within Midgard should be discarded entirely.

Time zones

There are two different options for time zone handling:

Storage in local time, inclusion of time zone information into the date formats
Storage in UTC

For now the prior option seems more natural, but UTC storage would make replication across timezones easier.