TIMESTAMP problem in GnuCash Postgresql backend

[Derek Atkins]

Matthew Vanecek <mevanecek at yahoo.com> writes:

> I forgot to mention, the dates that the engine gives to the Postgres
> backend are local time dates, not GMT.  If I enter 07/25/2003 in the
> register, the backend receives "2003-07-25 00:00:00".  The file backend
> assumes (correctly) that 07/25/2003 is a local-time date.  The 

Well, no, the file backend receives a Timespec representing that time
in localtime.  It does not receive a string..  But let's ignore that
nitpick for a moment and use the string since it's earlier for us
humans.

Let's say that you get the string "2003-07-25 00:00:00-05" but you're
in the US/Pacific timezone -- how would that get displayed?

Now, what about if you got the gmt string string "2003-07-25 05:00:00"
but you're in the US/Pacific timezone?  How would _THAT_ get
displayed?

I would assert that:

a) if you're in the US/Eastern timezone, both of these timestamps would
   be displayed exactly the same, and
2) if you're in the US/Pacific timezone, both of these timestamps would
   STILL be displayed exactly the same.
3) If you're in any other timezone, both of these timestamps would be
   displayed "correctly" by converting to the local timezone.

There is absolutely no ambiguity in a GMT timestamp -- you just need
to convert back and forth from GMT to localtime at the "border".  In
fact, you could just use gnc_iso8601_to_timespec_gmt() to convert the
string (in GMT format) to a Timespec.  The only problem is that there
is (currently) no API to _print_ an iso8601 time in GMT format.

> Also, defining the column as "TIMESTAMP WITH TIME ZONE" means that the
> inserted date is always stored in local time (e.g., '2003-07-25 
> 00:00:00+06' is stored as '2003-07-24 13:00:00-05', in my TZ), where
> defining "TIMESTAMP" as the column data type strips time zone
> information gratuitously ('2003-07-25  00:00:00+06' is stored as
> '2003-07-25 00:00:00').

See my previous message about why storing with a timezone can be
problematic.  Yes, just ignoring the timezone is worse -- there is
definitely something that needs to be fixed here.  However I feel the
easiest thing to do is just store the data in GMT, and convert it
back and forth in the backend.

> Just some info on the subtleties of SQL time data types....I'd prefer to
> keep time zone information stored with the timestamp.

Why?
Who cares if you store "2003-07-25 00:00:00-05" or "2004-07-25 05:00:00"?

-derek
-- 
       Derek Atkins, SB '93 MIT EE, SM '95 MIT Media Laboratory
       Member, MIT Student Information Processing Board  (SIPB)
       URL: http://web.mit.edu/warlord/    PP-ASEL-IA     N1NWH
       warlord at MIT.EDU                        PGP key available