Quote:
Originally Posted by kovidgoyal
dates are always stored as full timestamps internally, regardless of their display representation. As for the different formats, that is likely an artifact of the catalog creation code. Dates are always stored in UTC internally, although as long as full timezone offset information is present, it does not matter.
|
The different 'formats' for 2015-01-01 are also in the metadata.opf, and they're what I see in metadata.db with SQLite Browser, so I have to question that the difference is an 'artefact of catalogue creation'.
It seems to me that the internal value for timestamp (Date) and pubdate (Published) are carried as entered, along with the locale offset from UTC. However, values for dates that I define are normalised to UTC time by applying the offset. Surely all dates should be subject to the same rules.
And it does matter, in order to get something sensible into a spreadsheet I need to have a template derived custom column the converts the date to a string. To do anything with dates in a spreadsheet requires a string 'format' that the spreadsheet software can cope with - eg to calculate the elapsed period between the publication of book A and book B.
Unravelling values like '2014-12-31T23:15:49+00:00' and 2012-07-23T00:00:00+10:00 in excel to get back to the values I entered - '2015 01 01' and 2012-07-23 - is vexing.
As things stand, any dates that I want in a csv require two columns, one that I can edit in calibre, and another that I can dump into a csv.
If I could apply a formatting template to columns when the csv catalogue is written - as I can for EPUB and MOBI catalogues - then I wouldn't have to carry the 'derived columns' in the database. I appreciate they're calculated as needed, but it irritates me that I have to carry the complexity of additional columns to put something in a CSV catalogue that I would not need for an EPUB or MOBI catalogue.
BR