wiki:dbPersistingTime
Last modified 11 years ago Last modified on 01/25/2008 04:49:42 PM

Persisting Time

LSST Database

Facts:

  • We need to deal with subsecond resolution, at least in some parts of the system (exposure). This is required by MOPS.
  • There are many different ways to represent time, eg in persistent world: native sql types (DATETIME, TIME, TIMESTAMP) or INT, BIGINT, etc, in transient world: int, int64, etc

Currently in the schema we need to store time information in

  • Alert (timeGenerated)
  • Object (earliestObsTime, latestObsTime)
  • Source (taiMidPoint, taiRange)
  • Science_CCD_Exposure (dateObs)
  • Raw_CCD_Exposure (dateObs. taiObs, mjdObs)
  • Calibration_CCD_Exposure (dateObs)
  • plus validityBegin validityEnd for the provenance related configurations

Our current assuption is that the *_Exposure tables need sub-second precision, and the rest can use second-resolution, and sub-second precision can be obtained by appropriate join(s).

Issue: mysql does not support sub-second resolution. (for more on mysql types click here). To store miliseconds, most users use BIGINT, however that means that date manipulation via mysql tools becomes harder. Coral is representing time using int64, and we will likely deal with time in transient classes using int or int64. How should we represent time in transient and in persistent classes? BTW, most other DBMSes do support miliseconds, eg Oracle has TIMESTAMP, SQL Server has DATEPART.

We also need to clarify which time we use where (MJD vs TAI)