# Switch butler schema to use TAI nanoseconds rather than datetime

XMLWordPrintable

## Details

• Type: Story
• Status: Done
• Resolution: Done
• Fix Version/s: None
• Component/s:
• Labels:
• Story Points:
4
• Sprint:
DB_S20_02
• Team:
Data Access and Database
• Urgent?:
No

## Description

Following discussion on DM-15760, we would like to try changing the database schemas to use TAI MJD as a single double precision number, rather than a datetime field. We have to investigate whether sqlalchemy can support full double precision to represent MJDs (full precision gives about 1 ms accuracy), we should also determine whether the user interface should require MJDs as Python floats in all cases, or if the user could use astropy.time.Time everywhere (which understands TAI), converting to and from MJD at the database table layer.

## Activity

Hide
Andy Salnikov added a comment -

I think this is ready for review. Jenkins builds OK for lsst_distrib+ci_hsc_gen3. One thing that worries me now and that we did not discuss on RFC is the range of dates we should represent. I have set it arbitrarily to 1970-01-01 and 2038-01-19 (mostly based on what I saw in the existing code) but we know that there are dates in imsim beyond that range. 2038 as not that far in the future, should we consider extending that to some reasonable father date? Should we extend it back in time too (epoch can still be 1/1/1970, we'll just use negative deltas)?

Jim Bosch, Kian-Tat Lim, you are also very welcome to look at PRs, they are here:

Show
Andy Salnikov added a comment - I think this is ready for review. Jenkins builds OK for lsst_distrib+ci_hsc_gen3. One thing that worries me now and that we did not discuss on RFC is the range of dates we should represent. I have set it arbitrarily to 1970-01-01 and 2038-01-19 (mostly based on what I saw in the existing code) but we know that there are dates in imsim beyond that range. 2038 as not that far in the future, should we consider extending that to some reasonable father date? Should we extend it back in time too (epoch can still be 1/1/1970, we'll just use negative deltas)? Jim Bosch , Kian-Tat Lim , you are also very welcome to look at PRs, they are here: https://github.com/lsst/daf_butler/pull/244 https://github.com/lsst/obs_base/pull/214 https://github.com/lsst/obs_subaru/pull/262 https://github.com/lsst/obs_lsst/pull/193
Hide
Tim Jenness added a comment -

Looks great. I moved the instrument files in obs_lsst and obs_subaru but hopefully git will work that out on rebase.

Show
Tim Jenness added a comment - Looks great. I moved the instrument files in obs_lsst and obs_subaru but hopefully git will work that out on rebase.
Hide
Andy Salnikov added a comment -

Tim suggested 2100 (2100-01-01?) or higher as the maximum date range, are there are any other suggestions? I think it is important on trying to fix the range early as these dates will be fixed forever in the database (I imagine at some point we will stop regenerating data for every new software release and keep databases longer). If there are no other ideas I'll fix it at 2100-01-01 (for now).

Show
Andy Salnikov added a comment - Tim suggested 2100 (2100-01-01?) or higher as the maximum date range, are there are any other suggestions? I think it is important on trying to fix the range early as these dates will be fixed forever in the database (I imagine at some point we will stop regenerating data for every new software release and keep databases longer). If there are no other ideas I'll fix it at 2100-01-01 (for now).
Hide
Andy Salnikov added a comment -

So max. time definition will now look like:

 MAX_TIME = astropy.time.Time("2100-01-01 00:00:00", format="iso", scale="tai") """Maximum time value that we can store. Assuming 64-bit integer field we can actually store higher values but we intentionally limit it to arbitrary but reasonably high value. Note that this value will be stored in registry database for eternity, so it should not be changed without proper consideration. """ 

Show
Andy Salnikov added a comment - So max. time definition will now look like: MAX_TIME = astropy.time.Time( "2100-01-01 00:00:00" , format = "iso" , scale = "tai" ) """Maximum time value that we can store. Assuming 64-bit integer field we can actually store higher values but we intentionally limit it to arbitrary but reasonably high value. Note that this value will be stored in registry database for eternity, so it should not be changed without proper consideration. """
Hide
Andy Salnikov added a comment -

Final Jenkins was successful, merged all four packages. Closing.

Show
Andy Salnikov added a comment - Final Jenkins was successful, merged all four packages. Closing.

## People

• Assignee:
Andy Salnikov
Reporter:
Tim Jenness
Reviewers:
Tim Jenness
Watchers:
Andy Salnikov, Christopher Stephens, Christopher Waters, Jim Bosch, Kian-Tat Lim, Michelle Gower, Tim Jenness