The Gen3 middleware will enable the use of time in the query/selector used for quantum graph generation. This requires times to be present in the registry database and means to be provided for users to specify times.
There are four obvious candidates for the representation of times in the registry database:
- Database internal DATETIME/TIMESTAMP fields, typically required to be UTC. Allows use of database functions for handling times.
- astropy.time internal format composed of two double-precision floating-point numbers representing an MJD in either UTC or TAI timescales.
- A single double-precision floating-point number representing an MJD, in UTC or TAI.
- lsst.daf.base.DateTime internal format composed of a single 64-bit integer counting nanoseconds since the Unix epoch in either UTC or TAI timescales.
Similarly, there are three candidates for human-specified time literals:
- ISO8601 string (YYYY-MM-DDTHH:mm:ss.ssssssZ).
- An MJD numeric literal, possibly with a prefix or suffix to indicate the type.
- A nanosecond numeric literal, again possibly with a prefix/suffix.
There are implementation desires for the time literal to be identifiable as such to the parser and for all time literals to be translated into a single internal representation within the quantum graph generator.
I think it is also desirable for the quantum graph selector expression to be close to ADQL/SQL rather than inventing a new language. Our requirements say that user-facing times do not need to be TAI, but pipeline-internal times should be TAI.
Note that the database representation can differ between the registry database and any metadata database published to the DAC and science users, although we may be able to reduce duplication by having those two be implemented using a single underlying set of database tables.
I propose that the human-specified literals be either ISO8601 with explicit timezone indcator or MJD (single-column) numeric, both in the UTC timescale. If a type indicator is necessary for the latter, prefixing the number with the literal string "MJD" could be acceptable, though it deviates from SQL/ADQL. I also expect these literal forms to be used in FITS headers where needed. Literal nanoseconds is too user-hostile to be supported.
As long as the registry database is non-public, I propose that the database representation be either DB-native DATETIME in UTC or lsst.daf.base.DateTime integer nanoseconds, assuming that the DateTime class can be used and new time-handling/translation code is not needed. The schema creator should choose between native and nanoseconds depending on the expected usage of the column.
Since DateTime provides conversions to and from UTC and MJD, it can be used as the internal representation within the parser and quantum graph generator.