More detailed treatment of time datatypes

[KylePoe]

trafficstars

Feature Request

Problem

• Currently, the TIME MySQL type is used to store the datajoint time type.
• There is also currently no support for a timedelta type.
• Datajoint queries of time types return datetime.timedelta types.
• This is because the TIME MySQL type is made to support both time and timedelta types, and PyMySQL resolves this by defaulting to timedelta.
• Since the DataJoint time type nominally only supports HH:MM:SS "time of day", this should be reflected by casting this return to a datetime.time object.
• Although time types are returned as timedelta objects, attempting to insert a timedelta object results in an AttributeError, as a translation method for datetime.timedelta -> time has not been implemented.
• Given the underlying support for timedelta types in the domain [-838:59:59, 838:59:59] and the meaningfulness of timedelta as a datatype, a timedelta type should be implemented which is also stored as a TIME type in the underlying database.

Requirements

There are a couple viable solutions. Here are the cases I envision:

• If timedelta is implemented, and I think it should, queries of time types should be returned as datetime.time.
• If timedelta is not implemented, time should be expanded to support timedelta types. To these ends, a translation method for datetime.timedelta -> time should be implemented.
• If timedelta is not implemented and time is not expanded to support timedelta types, then there is no reason that queries of time should not be altered to return datetime.time objects.

Justification

The concept of duration is ubiquitous in science. Given that this type is supported as both native python datetime.timedelta and the underlying MySQL type TIME, this should certainly be supported by DataJoint.

Furthermore, objects returned by fetch operations should constitute valid objects for the insert operation. For at least the time type, this is not the case.

Reproduction

In datajoint_python version 13.2:

• Create a schema with a table containing a time attribute such as

# test_schema.py
@schema
class Test(dj.Manual):
  definition = """
  test_id: int
  ---
  test_time: time
  """

• After importing the schema, call insert on the table:

  from test_schema import Test
  import datetime

  Test.insert1({'test_id': 1, 'test_time': datetime.datetime.now().time())}) # Should be fine
  test_query = Test & 'test_id=1' # Query for inserted item
  test_item = test_query.fetch1() # Fetch item
  assert isinstance(test_item['test_time'], datetime.timedelta) # Assertion succeeds
  test_query.delete()
  Test.insert1(test_item) # raises AttributeError