[GRASS-dev] timestamp problems with PG driver

[Markus Neteler]

On Tue, Jul 20, 2010 at 5:02 PM, Anne Ghisla <a.ghisla at gmail.com> wrote:
> On Mon, Jul 19, 2010 at 3:25 PM, Markus Neteler <neteler at osgeo.org> wrote:
>> Hi,
>>
>> I am connected to a PostgreSQL database with a timestamp column.
>> While column type connection etc works there is a problem with the
>> leading "0".
>>
>> The original timestamp
>> 2010-03-26 05:31:25
>> is printed by v.db.select as
>> 2010-3-26 5:31:25
>>
>> Below an excerpt of debug output:
>>
>> D3/3: row = 99 nrows = 100
>> D3/3: row 99, col 0, gpgtype 8, sqltype 6: val = '38.1958365'
>> D3/3: row 99, col 1, gpgtype 8, sqltype 6: val = '15.9744719'
>> D3/3: row 99, col 2, gpgtype 3, sqltype 3: val = '976451'
>> D3/3: row 99, col 9, gpgtype 16, sqltype 11: val = '2010-03-26
>> 05:31:25'  <<--- correctly fetched by PG driver
>> D3/3: Row fetched
>> D0/3: sqltype: 6
>> D0/3: Fetched valuestring: 38.1958365
>> D0/3: sqltype: 6
>> D0/3: Fetched valuestring: 15.9744719
>> D0/3: sqltype: 3
>> D0/3: Fetched valuestring: 976451
>> D0/3: sqltype: 6
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 3
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 3
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 6
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 3
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 3
>> D0/3: Fetched valuestring:
>> D0/3: sqltype: 11
>> D0/3: Fetched valuestring: 2010-3-26 5:31:25    <---
>> db_convert_column_value_to_string() failed
>> 38.1958365|15.9744719|976451|||||||2010-3-26 5:31:25
>>
>> As far as I understand, the formatting is getting lost in
>>
>> lib/db/dbmi_base/columnfmt.c
>> db_convert_column_value_to_string()
>>
>> which calls
>> db_get_column_value()
>>
>> Is a kind of internal string conversion needed to maintain the leading "0"?
>> No clue...
>
> As far as I understand C,
>
> db_convert_column_value_to_string()
>
> calls
> lib/db/dbmi_base/valuefmt.c
> db_convert_value_to_string()
>
> that calls, in the datetime case:
> lib/db/dbmi_base/datetime.c
> db_convert_value_datetime_into_string()
>
> I propose r42866 (trunk), backported to 6.5 and 6.4.

Now it lokos good:

v.db.select import_ed
...
58.157727|12.4292441|308824|||||||2008-03-26 14:01:11
46.0114738|11.0810102|74387|||||||2005-01-26 08:01:42
46.1332642|-0.3797519|295130|||||||2008-03-03 03:21:18.000288

thanks, Anne!

Markus