Re: Upgrade from RPostgresql 0.3-3 to 0.4 (just this week) breaks our query - TIMESTAMP not coming through correctly?
21 views
Skip to first unread message
NISHIYAMA Tomoaki
Jun 18, 2014, 10:25:42 PM6/18/14
to Mark Dalphin, NISHIYAMA Tomoaki, rpostgr...@googlegroups.com
Dear Mark,
The probable change related is r242
https://code.google.com/p/rpostgresql/source/detail?r=242
The problem with the older code was that it disregards the time zone.
It looks as if there is no problem because the data stored in the db
and the time zone you are operating matched.
The reason the new code does not work with your data seems that
the strptime in your system does not support +13:00 time zone properly.
https://bugs.r-project.org/bugzilla/show_bug.cgi?id=15768
> as.POSIXct("2014-03-18 14:52:01+1200", format="%Y-%m-%d %H:%M:%OS%z")
[1] "2014-03-18 11:52:01 JST"
> as.POSIXct("2014-03-18 14:52:01+1300", format="%Y-%m-%d %H:%M:%OS%z")
[1] NA
As a workaround you might do without the time zone conversion,
but the root cause is perhaps in glibc.
https://sourceware.org/bugzilla/show_bug.cgi?id=16141
--
Tomoaki NISHIYAMA
Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan
On 2014/06/19, at 9:43, Mark Dalphin wrote:
> Dear Tomoaki Nishiyama,
>
> I've gotten your name from the CRAN list of package maintainers. Please let me know if I should be contacting someone else.
>
> We are running old systems because of various regulatory requirements, so this bug report might only apply to older systems. I can easily get you details on aspects of the (multiple) systems involved if you need them.
>
> For some reason TIMESTAMP columns are coming back as "NA" (or NULL) using the new RPostgresql driver, while the older one did fine. The error is shown below:
>
> We have a TABLE, "Reagent_AUDIT" that looks like this:
> => \d reagent_audit
> Table "public.reagent_audit"
> Column | Type | Modifiers
> ------------------+--------------------------+--------------------------------------------------------
> auditid | integer | not null default nextval('reagent_auditseq'::regclass)
> who | text | not null default "current_user"()
> what | character(1) | not null
> whenchanged | timestamp with time zone | not null default now()
> reagentid | integer |
> rgntype | text |
> site | text | default 'PEDNZ'::text
> rgnname | text |
> expirydate | timestamp with time zone |
> qcstatus | text |
> evidenceid | integer |
> mfgid | text |
> mfgcatalognumber | text |
> mfglotnumber | text |
> wholastchanged | text |
> whenlastchanged | timestamp with time zone |
> Indexes:
> "reagent_audit_pkey" PRIMARY KEY, btree (auditid)
> Check constraints:
> "reagent_audit_what_check" CHECK (what = 'D'::bpchar OR what = 'U'::bpchar OR what = 'I'::bpchar)
>
>
> Working within R, with a DB connection ("conn") to that database, I get different results between the older and new RPostgreSQL drivers:
>
> NEW (0.4) driver
> dbGetQuery(conn, "select * from reagent_audit where rgnname='BUF140312'")
> auditid who what whenchanged reagentid rgntype site rgnname expirydate qcstatus evidenceid mfgid mfgcatalognumber
> 1 640 lotnumber I <NA> 90 Buf PEDNZ BUF140312 <NA> HOLD 2 ROC <NA>
> 2 645 lotnumber U <NA> 90 Buf PEDNZ BUF140312 <NA> HOLD 169 ROC <NA>
> 3 650 lotnumber U <NA> 90 Buf PEDNZ BUF140312 <NA> PASS 169 ROC <NA>
> mfglotnumber wholastchanged whenlastchanged
> 1 10205100 <NA> <NA>
> 2 10205100 <NA> <NA>
> 3 10205100 <NA> <NA>
>
> OLD (0.3-3) driver
> dbGetQuery(conn, "select * from reagent_audit where rgnname='BUF140312'")
> auditid who what whenchanged reagentid rgntype site rgnname expirydate qcstatus evidenceid mfgid
> 1 640 lotnumber I 2014-03-18 14:52:01 90 Buf PEDNZ BUF140312 2014-11-30 HOLD 2 ROC
> 2 645 lotnumber U 2014-03-18 14:52:43 90 Buf PEDNZ BUF140312 2014-11-30 HOLD 169 ROC
> 3 650 lotnumber U 2014-03-18 14:53:00 90 Buf PEDNZ BUF140312 2014-11-30 PASS 169 ROC
> mfgcatalognumber mfglotnumber wholastchanged whenlastchanged
> 1 <NA> 10205100 <NA> <NA>
> 2 <NA> 10205100 <NA> <NA>
> 3 <NA> 10205100 <NA> <NA>
>
> Working with "psql" shows:
> > select * from reagent_audit where rgnname='BUF140312';
> auditid | who | what | whenchanged | reagentid | rgntype | site | rgnname | expirydate | qcs
> tatus | evidenceid | mfgid | mfgcatalognumber | mfglotnumber | wholastchanged | whenlastchanged
> ---------+-----------+------+-------------------------------+-----------+---------+-------+-----------+------------------------+----
> ------+------------+-------+------------------+--------------+----------------+-----------------
> 640 | lotnumber | I | 2014-03-18 14:52:01.624169+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | HOL
> D | 2 | ROC | | 10205100 | |
> 645 | lotnumber | U | 2014-03-18 14:52:43.152737+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | HOL
> D | 169 | ROC | | 10205100 | |
> 650 | lotnumber | U | 2014-03-18 14:53:00.916409+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | PAS
> S | 169 | ROC | | 10205100 | |
> (3 rows)
>
>
> R version information:
>
> R version 2.15.3 (2013-03-01)
> Platform: i686-pc-linux-gnu (32-bit)
>
> locale:
> [1] LC_CTYPE=en_NZ.UTF-8 LC_NUMERIC=C LC_TIME=en_NZ.UTF-8 LC_COLLATE=en_NZ.UTF-8
> [5] LC_MONETARY=en_NZ.UTF-8 LC_MESSAGES=en_NZ.UTF-8 LC_PAPER=C LC_NAME=C
> [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=en_NZ.UTF-8 LC_IDENTIFICATION=C
>
> attached base packages:
> [1] stats graphics grDevices utils datasets methods base
>
> other attached packages:
> [1] LotNumberDB_1.15 RPostgreSQL_0.3-3 DBI_0.2-7
>
> Thank-you for maintaining this package; I started to at one point around 2007 or 2008 and it was way more work than I could afford.
>
> Regards,
> Mark Dalphin
>
> --
> <logo_small.gif>
> Mark Dalphin Ph.D.
> Director of Bioinformatics
> mark.d...@pacificedge.co.nz
> Ph: +64-3-479-5805
> Cell: +64-21-156-7625
> Skype: mark.dalphin.pel
> <facebook.gif> <twitter.gif> <youtube.gif>
> <line.gif>
> 87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz
> <mark_dalphin.vcf>
The probable change related is r242
https://code.google.com/p/rpostgresql/source/detail?r=242
The problem with the older code was that it disregards the time zone.
It looks as if there is no problem because the data stored in the db
and the time zone you are operating matched.
The reason the new code does not work with your data seems that
the strptime in your system does not support +13:00 time zone properly.
https://bugs.r-project.org/bugzilla/show_bug.cgi?id=15768
> as.POSIXct("2014-03-18 14:52:01+1200", format="%Y-%m-%d %H:%M:%OS%z")
[1] "2014-03-18 11:52:01 JST"
> as.POSIXct("2014-03-18 14:52:01+1300", format="%Y-%m-%d %H:%M:%OS%z")
[1] NA
As a workaround you might do without the time zone conversion,
but the root cause is perhaps in glibc.
https://sourceware.org/bugzilla/show_bug.cgi?id=16141
--
Tomoaki NISHIYAMA
Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan
On 2014/06/19, at 9:43, Mark Dalphin wrote:
> Dear Tomoaki Nishiyama,
>
> I've gotten your name from the CRAN list of package maintainers. Please let me know if I should be contacting someone else.
>
> We are running old systems because of various regulatory requirements, so this bug report might only apply to older systems. I can easily get you details on aspects of the (multiple) systems involved if you need them.
>
> For some reason TIMESTAMP columns are coming back as "NA" (or NULL) using the new RPostgresql driver, while the older one did fine. The error is shown below:
>
> We have a TABLE, "Reagent_AUDIT" that looks like this:
> => \d reagent_audit
> Table "public.reagent_audit"
> Column | Type | Modifiers
> ------------------+--------------------------+--------------------------------------------------------
> auditid | integer | not null default nextval('reagent_auditseq'::regclass)
> who | text | not null default "current_user"()
> what | character(1) | not null
> whenchanged | timestamp with time zone | not null default now()
> reagentid | integer |
> rgntype | text |
> site | text | default 'PEDNZ'::text
> rgnname | text |
> expirydate | timestamp with time zone |
> qcstatus | text |
> evidenceid | integer |
> mfgid | text |
> mfgcatalognumber | text |
> mfglotnumber | text |
> wholastchanged | text |
> whenlastchanged | timestamp with time zone |
> Indexes:
> "reagent_audit_pkey" PRIMARY KEY, btree (auditid)
> Check constraints:
> "reagent_audit_what_check" CHECK (what = 'D'::bpchar OR what = 'U'::bpchar OR what = 'I'::bpchar)
>
>
> Working within R, with a DB connection ("conn") to that database, I get different results between the older and new RPostgreSQL drivers:
>
> NEW (0.4) driver
> dbGetQuery(conn, "select * from reagent_audit where rgnname='BUF140312'")
> auditid who what whenchanged reagentid rgntype site rgnname expirydate qcstatus evidenceid mfgid mfgcatalognumber
> 1 640 lotnumber I <NA> 90 Buf PEDNZ BUF140312 <NA> HOLD 2 ROC <NA>
> 2 645 lotnumber U <NA> 90 Buf PEDNZ BUF140312 <NA> HOLD 169 ROC <NA>
> 3 650 lotnumber U <NA> 90 Buf PEDNZ BUF140312 <NA> PASS 169 ROC <NA>
> mfglotnumber wholastchanged whenlastchanged
> 1 10205100 <NA> <NA>
> 2 10205100 <NA> <NA>
> 3 10205100 <NA> <NA>
>
> OLD (0.3-3) driver
> dbGetQuery(conn, "select * from reagent_audit where rgnname='BUF140312'")
> auditid who what whenchanged reagentid rgntype site rgnname expirydate qcstatus evidenceid mfgid
> 1 640 lotnumber I 2014-03-18 14:52:01 90 Buf PEDNZ BUF140312 2014-11-30 HOLD 2 ROC
> 2 645 lotnumber U 2014-03-18 14:52:43 90 Buf PEDNZ BUF140312 2014-11-30 HOLD 169 ROC
> 3 650 lotnumber U 2014-03-18 14:53:00 90 Buf PEDNZ BUF140312 2014-11-30 PASS 169 ROC
> mfgcatalognumber mfglotnumber wholastchanged whenlastchanged
> 1 <NA> 10205100 <NA> <NA>
> 2 <NA> 10205100 <NA> <NA>
> 3 <NA> 10205100 <NA> <NA>
>
> Working with "psql" shows:
> > select * from reagent_audit where rgnname='BUF140312';
> auditid | who | what | whenchanged | reagentid | rgntype | site | rgnname | expirydate | qcs
> tatus | evidenceid | mfgid | mfgcatalognumber | mfglotnumber | wholastchanged | whenlastchanged
> ---------+-----------+------+-------------------------------+-----------+---------+-------+-----------+------------------------+----
> ------+------------+-------+------------------+--------------+----------------+-----------------
> 640 | lotnumber | I | 2014-03-18 14:52:01.624169+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | HOL
> D | 2 | ROC | | 10205100 | |
> 645 | lotnumber | U | 2014-03-18 14:52:43.152737+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | HOL
> D | 169 | ROC | | 10205100 | |
> 650 | lotnumber | U | 2014-03-18 14:53:00.916409+13 | 90 | Buf | PEDNZ | BUF140312 | 2014-11-30 00:00:00+13 | PAS
> S | 169 | ROC | | 10205100 | |
> (3 rows)
>
>
> R version information:
>
> R version 2.15.3 (2013-03-01)
> Platform: i686-pc-linux-gnu (32-bit)
>
> locale:
> [1] LC_CTYPE=en_NZ.UTF-8 LC_NUMERIC=C LC_TIME=en_NZ.UTF-8 LC_COLLATE=en_NZ.UTF-8
> [5] LC_MONETARY=en_NZ.UTF-8 LC_MESSAGES=en_NZ.UTF-8 LC_PAPER=C LC_NAME=C
> [9] LC_ADDRESS=C LC_TELEPHONE=C LC_MEASUREMENT=en_NZ.UTF-8 LC_IDENTIFICATION=C
>
> attached base packages:
> [1] stats graphics grDevices utils datasets methods base
>
> other attached packages:
> [1] LotNumberDB_1.15 RPostgreSQL_0.3-3 DBI_0.2-7
>
> Thank-you for maintaining this package; I started to at one point around 2007 or 2008 and it was way more work than I could afford.
>
> Regards,
> Mark Dalphin
>
> --
> <logo_small.gif>
> Mark Dalphin Ph.D.
> Director of Bioinformatics
> mark.d...@pacificedge.co.nz
> Ph: +64-3-479-5805
> Cell: +64-21-156-7625
> Skype: mark.dalphin.pel
> <facebook.gif> <twitter.gif> <youtube.gif>
> <line.gif>
> 87 St David St, PO Box 56, Dunedin, New Zealand 9016www.pacificedge.co.nz
> <mark_dalphin.vcf>
NISHIYAMA Tomoaki
Jun 19, 2014, 12:02:32 AM6/19/14
to Mark Dalphin, NISHIYAMA Tomoaki, rpostgr...@googlegroups.com
Dear Mark,
If you need world-wide support,
SET timezone to 'UTC'
as http://www.postgresql.org/docs/9.3/static/sql-set.html
at the begining or
AT TIME ZONE zone 'UTC'
(as explained in http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)
for each query might help as a workaround option.
The data transfer is done as a character string anyway, and the problem is whether the
string can be interpreted as a proper time data.
--
Tomoaki NISHIYAMA
Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan
On 2014/06/19, at 12:02, Mark Dalphin wrote:
> Dear Tomoaki,
>
> Thank you for the incredibly prompt reply. I'm impressed with your tracking this down AND associating it so quickly with the glibc bug. I do need to use the time zones as this DB (should) work at many different sites around the world. I shall have to re-think my options here, perhaps using the PostgreSQL built-in functions to return an already formatted character string (or strings: one containing the time zone and the other the local time).
>
> Thanks again,
> Mark
If you need world-wide support,
SET timezone to 'UTC'
as http://www.postgresql.org/docs/9.3/static/sql-set.html
at the begining or
AT TIME ZONE zone 'UTC'
(as explained in http://www.postgresql.org/docs/9.2/static/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT)
for each query might help as a workaround option.
The data transfer is done as a character string anyway, and the problem is whether the
string can be interpreted as a proper time data.
--
Tomoaki NISHIYAMA
Advanced Science Research Center,
Kanazawa University,
13-1 Takara-machi,
Kanazawa, 920-0934, Japan
> Dear Tomoaki,
>
> Thank you for the incredibly prompt reply. I'm impressed with your tracking this down AND associating it so quickly with the glibc bug. I do need to use the time zones as this DB (should) work at many different sites around the world. I shall have to re-think my options here, perhaps using the PostgreSQL built-in functions to return an already formatted character string (or strings: one containing the time zone and the other the local time).
>
> Thanks again,
> Mark
>
> On 19/06/14 14:25, NISHIYAMA Tomoaki wrote:
>> Dear Mark,
>>
>> The probable change related is r242
>>
>> https://code.google.com/p/rpostgresql/source/detail?r=242
>>
>>
>> The problem with the older code was that it disregards the time zone.
>> It looks as if there is no problem because the data stored in the db
>> and the time zone you are operating matched.
>>
>> The reason the new code does not work with your data seems that
>> the strptime in your system does not support +13:00 time zone properly.
>>
>>
>> https://bugs.r-project.org/bugzilla/show_bug.cgi?id=15768
>>
>>
>>
>>> as.POSIXct("2014-03-18 14:52:01+1200", format="%Y-%m-%d %H:%M:%OS%z")
>>>
>> [1] "2014-03-18 11:52:01 JST"
>>
>>> as.POSIXct("2014-03-18 14:52:01+1300", format="%Y-%m-%d %H:%M:%OS%z")
>>>
>> [1] NA
>>
>> As a workaround you might do without the time zone conversion,
>> but the root cause is perhaps in glibc.
>>
>>
>> https://sourceware.org/bugzilla/show_bug.cgi?id=16141
>>
>>
>>
>
> --
> On 19/06/14 14:25, NISHIYAMA Tomoaki wrote:
>> Dear Mark,
>>
>> The probable change related is r242
>>
>> https://code.google.com/p/rpostgresql/source/detail?r=242
>>
>>
>> The problem with the older code was that it disregards the time zone.
>> It looks as if there is no problem because the data stored in the db
>> and the time zone you are operating matched.
>>
>> The reason the new code does not work with your data seems that
>> the strptime in your system does not support +13:00 time zone properly.
>>
>>
>> https://bugs.r-project.org/bugzilla/show_bug.cgi?id=15768
>>
>>
>>
>>> as.POSIXct("2014-03-18 14:52:01+1200", format="%Y-%m-%d %H:%M:%OS%z")
>>>
>> [1] "2014-03-18 11:52:01 JST"
>>
>>> as.POSIXct("2014-03-18 14:52:01+1300", format="%Y-%m-%d %H:%M:%OS%z")
>>>
>> [1] NA
>>
>> As a workaround you might do without the time zone conversion,
>> but the root cause is perhaps in glibc.
>>
>>
>> https://sourceware.org/bugzilla/show_bug.cgi?id=16141
>>
>>
>>
>
> --
Reply all
Reply to author
Forward
0 new messages