Start Time and End Time dropping leading 0

[tlammer]

Reported by CSIRO
The fields are described as documenting time in military format, but when "0600" is entered, it is saved and displayed as "600"

[maxpatiiuk]

What table did this happen for?

Unable to replicate for the Collecting Event table.

Maybe they had some error in their form definition?

[benanhalt]

I vaguely remember seeing something about this. Maybe time fields in the schema somewhere that are integer fields behind-the-scenes.

[benanhalt]

In collectingevent:

 StartTime                   | smallint(6)  | YES  |     | NULL    |                |

Maybe we should add some user definable timestamp fields that could be used for start and stop dates with times.

[tlammer]

DwC standard follows ISO 8601: https://en.wikipedia.org/wiki/ISO_8601#Time_zone_designators
Times in UTC with + or - designating offset. It looks like our time fields will accept - but not +

[maxpatiiuk]

@benanhalt Should we change the data model to convert that field into TIME, or should we just change the form to display that field as a time picker rather than a numeric field?

[benanhalt]

It seems like it goes together with the (start|end)date fields to produce a timestamp field. We could convert it to a time field, but then we have to worry about if it is already being used somewhere and how. I think it would work better just to deprecate the field and add a legit (start|end)timestamp field that includes the full date and time for those who want that precision. That way ordering of the records would work correctly.

[maxpatiiuk]

It seems like it goes together with the (start|end)date fields to produce a timestamp field

by "it goes together" you mean it should go together, or it currently goes together?

If I am correct, there are separate fields for StartDate and StartTime, but perhaps there should just be a single timestamp field for both.

Same for EndDate/EndTime.

That way ordering of the records would work correctly

Isn't ordering working correctly the way it is? Just sort by StartDate 1st and StartTime 2nd

Sorry, just trying to understand this better.

[benanhalt]

I mean it conceptually goes together.

It looks like dwc has a separate eventTime term (https://dwc.tdwg.org/list/#dwc_eventTime) so maybe the field should be converted to an SQL TIME type. Does the Specify 6 documentation have anything to say about how the current integer representation should be interpreted? Like is it minutes past midnight or what?