search

specify / specify6

Source Code for Specify 6, Biological Collections Management Platform
https://specifysoftware.org
GNU General Public License v2.0
14 stars 6 forks source link

Make export dates conform to ISO standards #828

Open tlammer opened 4 years ago

tlammer commented 4 years ago

Willem is working on getting his database to conform to industry standards and noted that the export date format does not conform to ISO standards: Currently, dates get exported as: 2007/09/3 The ISO standard would be: 2007-09-23T00:00:00 Further, I notice that partial dates are not being exported, whereas Darwin Core / ISO requires e.g. 2007-09. To work around this, I have to make a static copy of the cache (in MS Access), and copy my date fields into text fields, and then update the rows accordingly. So perhaps the cache table should format the date fields as ‘text’ rather than ‘date’.

timo11 commented 4 years ago

Partial dates are exported when the 'export' is used. partialdateexp

When querying the cache partial dates can be obtained by using the SQL generated by 'Show SQL'. For example: concat(year(dwckui.dateIdentified), case when month(dwckui.dateIdentified) > 0 then concat('-',lpad(month(dwckui.dateIdentified), 2, '0')) else '' end,case when day(dwckui.dateIdentified) > 0 then concat('-', lpad(day(dwckui.dateIdentified), 2, '0')) else '' end) as "dateIdentified"