search

cidgoh / DataHarmonizer

A standardized browser-based spreadsheet editor and validator that can be run offline and locally, and which includes templates for SARS-CoV-2 and Monkeypox sampling data. This project, created by the Centre for Infectious Disease Genomics and One Health (CIDGOH), at Simon Fraser University, is now an open-source collaboration with contributions from the National Microbiome Data Collaborative (NMDC), the LinkML development team, and others.
MIT License
91 stars 25 forks source link

Input widgets for date-times with different levels of granularity #249

Closed turbomam closed 2 years ago

turbomam commented 2 years ago

Currently a nice date picker is provided

NMDC will also have times, date-times and partial dates like 2020-02

ddooley commented 2 years ago

It was left undocumented, but https://github.com/cidgoh/DataHarmonizer/issues/137 covers a solution for the date component that may work for you? The main thing is that whatever the precision - day or month or year - if it is marked as an ISO date field, it must fill in the extra parts YYYY-MM-DD during validation, so this is a mechanism for passive date normalization where that can be done into ISO 8601 dates; so Im not sure if this fits your case. The image below shows that date formatting is being normalized row-by-row to the YYYY-01-01 or YYYY-MM-01 or YYYY-MM-DD degree of precision based on other field's " precision" setting. This approach supports dates destined for database date fields, that need date comparison for order at least, and in a sense matching granularity, e.g. year entries will match perfectly, month ones will too.

Here is the spreadsheet data before it gets opened in DataHarmonizer: image

When it is opened in DH, the dates get adjusted to match granularity: image

As well if some dates can't be matched to granularity (say month is missing but that's the granularity set) then DH will currently put an underscore value, e.g. "month" granularity but given date is a year 2020, then "2020--01" so that validation trips and user is drawn to specify the desired granularity.

If you simply name the "date ... unit" field to have a different suffix, like "... precision", then the whole automatic normalization (to first day of month, or first month) process is skipped: image

Code portions of main.js are: line 821 for matching [date field] + [date field unit] case, and line 849 for setDateChange().

I will now add documentation to Wiki about it.

ddooley commented 2 years ago

Does that seem workable (given that target dates are often SQL database fields)? Or should we create a new data type, say "partialDate" that can handle YYYY or YYYY-MM ?

ddooley commented 2 years ago

I've seen that NMDC has implemented year-only and year+month only fields, which is the alternate way to approach this. I'll close this within a few days if no objections.

turbomam commented 2 years ago

Yes, we're handling this with regular expressions and can accept the absence of point and click widgets for now. Thanks.