Re: [Gnumed-devel] Date of birth default times all seem to be 11:11

[Jim Busser]

On 2011-07-15, at 4:44 AM, Karsten Hilbert wrote:

> Most of the time no one actually documented the time of
> birth. Thus it was typically set to 0:00:00 at the local
> time zone. When saving to the backend it sometimes got
> converted to UTC and back to the local time zone (see the
> *12*:11:11 rather than 11:11:11 above) thereby moving back
> the DOB by one day.

What you originally attempted

        timestamp with timezone

and setting time (when not entered) to 0:00:00 sounds most appropriate.  A date 
of birth, we might agree, is definable instant (moment) in time on planet 
earth. Even though it is possible to make *this* column date-only (no time 
zone), I am worried a bug will still bite us on other datetimes!

Can we figure out what is going on? Note that I get different results from the 
same data set when I run psql compared to when I query from the Reports plugin… 
 (see screenshot). Note my server and client are on the same machine and OS 
(not running across a virtual machine, for example).

The difference in the output is not even just a uniform "shift", the difference 
is *variable*. Note how from the psql output, only the first two share the same 
UTC offset ( -08), whereas in the results of the query returned by the Reports 
plugin, the first *three* share the same offset and it is different from the 
psql results ( -09).

Presumably this cannot be a function of how it is stored, and it must instead 
be a function of the query? Anything in what the client would be doing that 
accounts for it?

gnumed_v15=> select dob from dem.identity;
            dob             
----------------------------
 1953-12-18 12:11:11.111-08
 1936-08-01 12:11:11.111-08
 1962-06-23 13:11:11.111-07
 1984-03-15 11:11:11.111-08
 1977-12-19 11:11:11.111-08
 1979-03-01 11:11:11.111-08
 1970-01-01 12:11:11.111-08
 1960-01-01 12:11:11.111-08
 1974-10-23 12:11:11.111-07
 1979-03-13 11:11:11.111-08
 1974-01-01 11:11:11.111-08
 1920-01-19 12:11:11.111-08
 1931-03-25 12:11:11.111-08
 1932-02-22 12:11:11.111-08
 1965-11-20 12:11:11.111-08
 1931-03-21 12:11:11.111-08
 1983-05-31 11:11:11.111-07
 1931-03-21 12:11:11.111-08
 1929-01-02 12:11:11.111-08
 
 1967-09-24 13:11:11.111-07
 1978-10-27 12:11:11.111-07
 1927-11-08 12:11:11.111-08
 1986-01-11 11:11:11.111-08
 1987-02-28 11:11:11.111-08
(25 rows)


-- Jim