price.date, transaction.post_date and neutral time

[John Ralls]

> On Feb 11, 2018, at 4:33 AM, Sébastien de Menten <sdementen at gmail.com> wrote:
> 
> When exporting data from SQL backends, I see some inconsistencies in the
> handling of some date & datetime columns.
> 
> In the prices table, when adding price via the price editor, I see in the
> date column a datetime with the UTC of the YYYY/MM/DD 00:00:00 of my local
> timezone (CET).
> For instance, for a price on 11/02/2018, I see  20180210230000, which is
> the UTC value for 11/02/2018 00:00:00+01:00.
> What is the reason of having the prices.date as a datetime type (vs a
> simple date type) ?
> Shouldn't it also be stored as  20180211105900, i.e. in neutral time as the
> field transaction.post_date ?
> 
> In the transactions table, the post_date is handled as a date in gnucash
> but stored also in a datetime type with the neutral time (10:59:00).
> So for a transaction on 11/02/2018, I see 20180211105900.
> What is the reason of having the transactions.post_date as a datetime type
> (vs a simple date type) ?
> 
> If the reason are mostly legacy, are there some plans to change that in 3.0
> ?
> _


The short answer is it’s legacy and while there are plans to perhaps change it, that didn’t happen in time for GnuCash 3.x and may not for 4.x.

They’re stored as TIMESTAMP because their internal representation is effectively time_t, and the internal representation is time_t because when it was written that’s what was available for time computations... in fact, until C++17’s std::chrono came along it and its companion struct tm were still the only standard time representations.

It’s an incredible amount of work to change the time representation. I started with 64-bit time and GDateTime in GnuCash 2.6; then we decided to divorce from GLib and so time that might have gone into reworking calculations got spent instead on converting to a C++ time implementation, which at least has the benefit of having an actual date representation integrated into it (GDate and GDateTime are completely orthogonal). As you might expect, calculations with post_date are pervasive throughout GnuCash and changing its representation will be a lot of work. That will happen in the course of C++ conversion and MVC cleanup, but like so many other things there’s a lot of preparation first, and frankly there are more important things to work on.

Regards,
John Ralls