A few months ago I batch uploaded a couple of hundred records from a spreadsheet. As far as I was aware the data went in fine.
Today I see that the records were all assigned the date 16/07/1905. I still have the spreadsheet, so I went back and checked, to find that (to save time I presume) I had given them all the date 2024. They were fairly low-value records from my perspective, mostly of common species which I figured might be useful for filling in monad distributions in an area which had no data at all, so I didn't think it was worth recording the exact date for each.
Now I face the task of correcting them all. iRecord has batched them based on grid refs I assume, so every one I edit might fix a few extra ones recorded at the same location, which is useful.
It would have been better all round if the system had rejected them all at the upload stage, telling me that the date was incorrect (that would have been an easy fix). That isn't what happened though.
Just thought I should let people know.
Comments
iRecord importer termlists for vague dates
The correct way of doing this from a spreadsheet is detailed at the bottom of this page: https://irecord.org.uk/help/import-termlists
However, it is perfectly reasonable to expect that some sensible error message be thrown when the spreadsheet does not conform. I have added this to our issue tracker: https://github.com/BiologicalRecordsCentre/iRecord/issues/1819
That guidance doesn't help…
That guidance doesn't help though - a year alone is not a date range (in the sense of the guidance - ie we're not importing with a date type, start and end columns, just a single date column).
This "problem" - detecting and parsing the format of dates supplied in excel spreadsheets was solved decades (?) ago in Recorder so I also (wrongly) thought it was safe to assume this was the case with iRecord, especially as it didn't complain.
It's also not an error in the supplied data - excel doesn't format the number 2024 to the date 16/07/1905 (unless you tell it to), this is presumably the iRecord import parser ignoring styles applied to the cell and treating everything in the date column as an excel date integer.
Response
The approach implied by the guidance is that a year is to be specified using a start and an end date (1st Jan and 31st Dec) -- see the 5th row of the table. This approach has been used for some time and, I believe, originated with the NBN.
It is certainly the case that the system could more intelligently parse other types of supplied data as you suggest. I have updated the GitHub issue linked above to note this.
A further issue of reading…
A further issue of reading the raw excel date integer also means that dates imported from excel spreadsheets created on macs prior to Office 2011 are four years out of date as they use a different 'start' date.
Date Ranges on NBN Atlas are not defined as in iRecord
Are people aware that date ranges in NBN work differently to those in iRecord, for example? The field in NBN Atlas is eventDate. The Darwin Core (DwC) format spec for this field, based on ISO 8601-1:2019, indicates that the 'end' value of the range (after the interval designator '/') is excluded from the range.
An example given is "1900/1909 (some time within the interval between the beginning of the year 1900 and before the year 1909)".
Another example is that the full range of year 2016 could be represented as either "2016" (not a range at all) or "2016-01/2017-01" or "2016-01-01/2017-01-01", but not as "2016-01-01/2017-12-31", as in https://irecord.org.uk/help/import-termlists, which would exclude 31st December.
For a discussion of eventDate see https://dwc.tdwg.org/list/#dwc_eventDate
(Note that Wikipedia article on ISO 8601 does not cover the topic comprehensively. Since the official ISO 8601 from ISO themselves is an expensive document I expect many are misled by its shoddy treatment in Wikipedia.)
The ISO 8601 convention is very good for one thing: subtracting the start value from the end value of the range gives its exact period/duration.
Note that the shorter the range the bigger the discrepancy as a proportion of the whole. The first 2 days of 2024 should be represented as the range "2024-01-01/2024-01-03". This range does not include 3rd Dec.
In conclusion, I suspect the various updates from iRecord into NBN Atlas do not currently make the necessary conversions. An example Odonata record is iRecord ID 20054240 of Aeshna mixta from BTO Garden BirdWatch within the week beginning 18/08/2019 shown as "Date seen: 18/08/2019 - 24/08/2019", meaning Sunday to Saturday inclusive. This appears on NBN Atlas and the date is shown as "Record Date: 2019-08-18 / 2019-08-24 (Day Range)" without conversion, implying only Sunday to Friday.
I can convert the ranges to the ISO 8601 format for the next update of my data resource dr731. Or am I barking up the wrong tree?
I'm not sure if this would work with a spreadsheet upload as I've not done that, but tucked away in the user settings, under edit then right at the bottom is a section on vague dates. It has a default to normal page settings ticked as standard, but you can change it to allow vague dates or never allow vague dates. Perhaps selecting the allow vague dates would prevent this happening in the future (still worth flagging here though as most people might not be aware of that setting).