Uploaded image for project: 'Data Management'
  1. Data Management
  2. DM-15890

Switch butler schema to use TAI nanoseconds rather than datetime

    Details

    • 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.

        Attachments

          Issue Links

            Activity

            Hide
            salnikov 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
            salnikov 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
            tjenness 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
            tjenness 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
            salnikov 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
            salnikov 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
            salnikov 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
            salnikov 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
            salnikov Andy Salnikov added a comment -

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

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

              People

              • Assignee:
                salnikov Andy Salnikov
                Reporter:
                tjenness Tim Jenness
                Reviewers:
                Tim Jenness
                Watchers:
                Andy Salnikov, Christopher Stephens, Christopher Waters, Jim Bosch, Kian-Tat Lim, Michelle Gower, Tim Jenness
              • Votes:
                0 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Summary Panel