Dates - Appear held to be inconsistent

[BetterRed]

This has long puzzled me, I was reminded of it by this ==>> How to format pubdate...

The attached screen shot overlays a csv catalogue on the calibre book list for a single book.

The Published and My Published columns are both formatted as 'yyyy MM dd', they were entered into the book list within a second of two of one another, both were entered as '2015 01 01' (without use of the calendar gadget).

So my questions are

1. Why do they have different internal representations.
   
2. And what does my local offset from UTC time have to do with the published date - currently +11, a week ago it was +10. Sydney's small beer when it comes to book publishing

BR

[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.

[BetterRed]

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

[kovidgoyal]

The date 2014-12-31T23:15:49+00:00 is in the international standard for date time string, ISO 8601

If your spreadsheet program cannot parse that, you need to get yourself a new spreadsheet program.

[BetterRed]

Yes Excel 2007 can parse it, if I tell it how, as in Parsing an ISO8601 date/time (including TimeZone) in Excel

I have too much investment in Excel to consider looking for a new spreadsheet program.

As someone commented in the above link - "(: this should be built in", but the fact is that's it's not. Maybe it is in 2010 or 2013 versions of Excel.

BR

[theducks]

I believe you can just open a new column in any Spreadsheet:

Insert a Substring(cell,4) formula referencing the imported ones field and then copy 'down' to all remaining cells (most adjust the cell references)

I don't know how to do it in OO, but Quattro Pro had a command to change formula cell contents to the calculated value


You have options once you learn your available tools

[BetterRed]

@theducks - Cut & paste won't to a conversion - there is no ISO Date format in Excel 2007, nor I suspect was there such a thing in Quattro, or the grandfather of all such things - Visicalc.

It is crazy that it's easier to pivot a table, or do an FFT in Excel than it is to deal with ISO dates - but that's the way it is I'm afraid.

If I use a general format eg 'yyyy-mm-dd' against the string 2014-12-31T23:15:49+00:00 it wont be reformatted, if I substr the 1st 10 chars I get 2014-12-31 as a date (value rather than a string) - but the value I entered into calibre was 2015-01-01!

I still don't understand why pubdate and #mydate are held inconsistently when I enter the same value.

I tried defining all the dates as 'iso' to see if it would the overcome the inconsistency . But when I do that I can't even enter a full date; month and year is all I can get, see attachment. And the peculiar value in My Published, that was what I got when I clicked Set "My Published' to today - I'm guessing this is a cutie five glitch.

I'll retain the template derived columns, they do work and I can use Excel's formatting features to get dates on which I can do arithmetic.

BR

[AnotherCat]

Originally Posted by BetterRed

Yes Excel 2007 can parse it, if I tell it how, as in Parsing an ISO8601 date/time (including TimeZone) in Excel

I assume that you are not doing it that way though as it can be done with a simple formula in a cell?

Mentioning it just in case.

[BetterRed]

@AnotherCat - the CSV catalogues are ad-hoc and ephemeral - IMO a VBA macro would be more easily reused than a cell formula. BTW the results of my inter-book arithmetic ends up in the calibre database via the Import List PI

But the custom column templates I have are trivial, and I don't discern any effect on performance on a 50K book library. So cell formulae and/or VBA macros are not needed, simply format the calibre created date-string column to an Excel date-value and watch them shift to the right

I was wishful thinking that rather than applying the formatting template in custom columns, I could apply it when I create the CSV catalogue, as I could if I were creating an EPUB Catalogue.

Leaving things as is the best solution - as I already said - it works.

BR