Re: [Gnumed-bugs] Unable to search by date in patient search field

[Karsten Hilbert]

On Sun, Jul 17, 2011 at 11:15:49AM -0700, Jim Busser wrote:

> 2011-07-17 11:13:10  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__log_PG_settings()
>  #1672): PG option [DateStyle]: ISO, DMY

...

> 2011-07-17 11:13:10  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__detect_client_timezone()
>  #272): trying to detect timezone from system
> 2011-07-17 11:13:11  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__expand_timezone()
>  #253): [PDT] maps to [America/Dawson]
> 2011-07-17 11:13:11  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__detect_client_timezone()
>  #289): candidates: [u'PDT', u'America/Dawson']
> 2011-07-17 11:13:11  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__validate_timezone()
>  #199): validating time zone [PDT]
> 2011-07-17 11:13:11  WARNING   gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__validate_timezone()
>  #220): time zone [PDT] is not settable
> 2011-07-17 11:13:11  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__validate_timezone()
>  #199): validating time zone [America/Dawson]
> 2011-07-17 11:13:11  INFO      gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__validate_timezone()
>  #209): time zone [America/Dawson] is settable
> 2011-07-17 11:13:11  INFO      gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__validate_timezone()
>  #215): time zone [America/Dawson] is usable
> 2011-07-17 11:13:11  INFO      gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__detect_client_timezone()
>  #306): client system time zone detected as equivalent to [America/Dawson]

...

> 2011-07-17 11:13:46  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::_generate_simple_query()
>  #249): [21.03.1931]: a DOB
> 2011-07-17 11:13:46  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::get_identities()
>  #81): running {'cmd': u"SELECT *, %s as match_type from dem.v_basic_person 
> WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, 
> %s::timestamp with time zone) order by lastnames, firstnames, dob", 'args': 
> ['date of birth', u'21.03.1931']}
> 2011-07-17 11:13:46  INFO      gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/wxpython/gmPatSearchWidgets.py::_on_enter()
>  #837): 0 matching person(s) found
> 2011-07-17 11:14:01  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::_generate_simple_query()
>  #249): [21/3/1931]: a DOB
> 2011-07-17 11:14:01  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::get_identities()
>  #81): running {'cmd': u"SELECT *, %s as match_type from dem.v_basic_person 
> WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, 
> %s::timestamp with time zone) order by lastnames, firstnames, dob", 'args': 
> ['date of birth', u'21/3/1931']}
> 2011-07-17 11:14:01  INFO      gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/wxpython/gmPatSearchWidgets.py::_on_enter()
>  #837): 0 matching person(s) found
> 2011-07-17 11:14:08  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::_generate_simple_query()
>  #249): [21/03/1931]: a DOB
> 2011-07-17 11:14:08  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::get_identities()
>  #81): running {'cmd': u"SELECT *, %s as match_type from dem.v_basic_person 
> WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, 
> %s::timestamp with time zone) order by lastnames, firstnames, dob", 'args': 
> ['date of birth', u'21/03/1931']}
> 2011-07-17 11:14:08  INFO      gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/wxpython/gmPatSearchWidgets.py::_on_enter()
>  #837): 0 matching person(s) found

This is all strange because

- GNUmed does detect that you entered a potential DOB
- it then selects an appropriate query
- the query runs fine

- but no matching rows are found despite we know there
  should be matching rows (namely, Kirk)

So, this asks for double-checking whether it should, indeed,
find rows:

        select dob from dem.identity;

> 2011-07-17 11:14:14  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::_generate_simple_query()
>  #249): [3/21/1931]: a DOB
> 2011-07-17 11:14:14  DEBUG     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::get_identities()
>  #81): running {'cmd': u"SELECT *, %s as match_type from dem.v_basic_person 
> WHERE dem.date_trunc_utc('day'::text, dob) = dem.date_trunc_utc('day'::text, 
> %s::timestamp with time zone) order by lastnames, firstnames, dob", 'args': 
> ['date of birth', u'3/21/1931']}
> 2011-07-17 11:14:14  ERROR     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::run_ro_queries()
>  #1133): query failed: [SELECT *, E'date of birth' as match_type from 
> dem.v_basic_person WHERE dem.date_trunc_utc('day'::text, dob) = 
> dem.date_trunc_utc('day'::text, E'3/21/1931'::timestamp with time zone) order 
> by lastnames, firstnames, dob]
> 2011-07-17 11:14:14  ERROR     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::run_ro_queries()
>  #1134): PG status message: 
> 2011-07-17 11:14:14  ERROR     gm.person 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py::get_identities()
>  #85): error running query
> Traceback (most recent call last):
>   File 
> "/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/business/gmPersonSearch.py", 
> line 83, in get_identities
>     rows, idx = gmPG2.run_ro_queries(queries = [query], get_col_idx=True)
>   File "/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py", 
> line 1121, in run_ro_queries
>     curs.execute(query['cmd'], args)
>   File 
> "/opt/local/Library/Frameworks/Python.framework/Versions/2.6/lib/python2.6/site-packages/psycopg2/extras.py",
>  line 122, in execute
>     return _cursor.execute(self, query, vars)
> DataError: date/time field value out of range: "3/21/1931"
> LINE 1: ...ay'::text, dob) = dem.date_trunc_utc('day'::text, E'3/21/193...
                                                             ^

This query fails due to PostgreSQL not being able to parse
"3/21/1931" as a timestamp which, in turn, is due to the
DateStyle setting:

> 2011-07-17 11:13:10  DEBUG     gm.db 
> (/Users/djb/Downloads/gnumed-client.0.9.7/Gnumed/pycommon/gmPG2.py::__log_PG_settings()
>  #1672): PG option [DateStyle]: ISO, DMY

just like the HINT says:

> HINT:  Perhaps you need a different "datestyle" setting.

The available choices would be DMY, YMD, and MDY. The letter
would tell PostgreSQL how to parse "3/21/1931". We can,
however, only choose one of the above (or else it would
simply be other strings that can't be parsed).

Karsten
-- 
GPG key ID E4071346 @ gpg-keyserver.de
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346