Ticket #405 (closed enhancement: fixed)

Opened 10 years ago

Last modified 7 years ago

Support sub-second time resolution in schema

Reported by: jbecla Owned by: Tim Axelrod
Priority: normal Milestone:
Component: dbSchema Keywords:
Cc: smm, fpierfed, taxelrod@… Blocked By:
Blocking: Project: LSST
Version Number:
How to repeat:

not applicable

Description (last modified by ktl) (diff)

all times should be in sub-second resolution, so we should probably use int64 instead of DATETIME, but this requires some research, e.g. perhaps double is better.

Change History

comment:1 Changed 10 years ago by jbecla

  • Status changed from new to assigned

comment:2 Changed 10 years ago by jbecla

  • Owner changed from jbecla to ktl

We decided to store time in TAI standard using BIGINT, where the last 6 digits represent microseconds. See DataAccWG telecon notes Oct 29, 2008, and "microsec support in database" thread on lsst-data.

Next step: write function that converts UNIX time to TAI (KT)

comment:3 Changed 10 years ago by jbecla

  • Milestone changed from DC3b Apps DB Schema to DC3b MW DB

comment:4 Changed 10 years ago by smm

MOPS uses some JPL libraries that expect UTC inputs.

Therefore, MOPS will need some means of translating from TAI quantities stored in the database to UTC values that can be passed into JPL code. Depending on whether MOPS would require such conversions often, it may be preferrable for MOPS to perform them application side using the lsst::daf::base::DateTime class rather than a stored function (a stored function is likely to be much slower than the native C/C++ implementation).

If for some reason this isn't feasible, then the other two options are:

  • add separate TAI<->UTC conversion routines to MOPS
  • embed native C/C++ conversion routines into the database using UDFs

This latter option should be a last resort: while many databases support UDFs, the in-database integration APIs are likely to be significantly different from database to database and would make database vendor transitions harder.

comment:5 Changed 10 years ago by ktl

  • Cc smm, fpierfed, taxelrod@… added
  • Status changed from assigned to needinfo
  • Description modified (diff)
  • Owner changed from ktl to Tim Axelrod

There are two issues here: one is changing persistence of DateTime objects in various classes to use TAI microseconds in a BIGINT instead of a MySQL DATETIME. This needs to occur in daf::persistence::DbStorageImpl and daf::persistence::DbTsvStorage. An epoch needs to be chosen; is the Unix epoch (1970-01-01 00:00:00 UTC) the correct one? This ticket is being assigned to TSA to determine this.

The second issue is implementing conversion functions from TAI to UTC and vice versa. Such functions are already available in the DateTime class, but they are not available in the database. As Serge mentioned, it is not ideal to implement these in the database, either as stored functions or (object code) UDFs. This task has been moved to a new ticket (#482).

comment:6 Changed 10 years ago by robyn


Have you pondered this issue on units? If so, sumarize your results and close the ticket.

comment:7 Changed 9 years ago by robyn

  • Status changed from needinfo to new

Changing owner from 'TimAxelrod' to 'Tim Axelrod'

comment:8 Changed 9 years ago by robyn

  • Status changed from new to assigned
  • Owner changed from TimAxelrod to Tim Axelrod

comment:9 Changed 8 years ago by ktl

  • Status changed from assigned to closed
  • Resolution set to fixed

Conversion functions are in the database, and DateTime objects are being adequately persisted into the database.

comment:10 Changed 7 years ago by robyn

  • Milestone DC3b MW DB deleted

Milestone DC3b MW DB deleted

Note: See TracTickets for help on using tickets.