Date/Time conversion from PostgreSQL to R fails

[GoogleCodeExporter]

What steps will reproduce the problem?

1. PostgreSQL Server 9.1 on Windows Server 2003 R2, datestyle = 'German'
2. R version 2.15.0, LC_TIME "German_Germany.1252"
3. > rs <- dbSendQuery(con,"select anytimefield from anytable")
4. > fetch(rs,-1)
   Fehler in as.POSIXlt.character(x, tz, ...) : 
   character string is not in a standard unambiguous format

Queries without temporal fields are aptly transmitted, so there seems to be an 
isolated problem with time conversion. Also, previous conversion of the 
timefield to varchar within the query works fine; e.g. "select 
anytimefield::varchar from anytable" returns a resultset. I would, however, 
rather not use this workaround, as my tables contain lot of date and timestamp 
fields.

Any suggestions on how to coax R into correctly converting dates?

Thank you in advance for helpful hints.

Sandor

Original issue reported on code.google.com by Sandor...@googlemail.com on 14 Oct 2012 at 3:01

[GoogleCodeExporter]

Hi Sandor

It so happens that Tomoaki just committed changes to SVN for datetime with 
timezone parsing.  Could you try the SVN version, please?

Also, could you provide us with a full "quoted" example to see what your German 
date strings look like, and if/how psql parses them correctly.

I only ever work in a (default US) English locale so I would not know if we 
need to tweak this at the R side, the PostgreSQL side or both.  But without a 
reproducible example we have nothing to go by.

Dirk

Original comment by dirk.eddelbuettel on 14 Oct 2012 at 4:06

[GoogleCodeExporter]

Dear Dirk,

thank you for answering ever so quickly. I think I solved the problem. But 
first your request:

The query 

> dbSendQuery(con,"select anytimefield::varchar from anytable")

returns:

> fetch(rs,2)
             anytimefield
1 14.10.2012 14:01:05.645
2 14.10.2012 14:01:05.661

As it seems, R cannot translate it to a Date/Time field. If, however, I prepend 
the assignment

> dbSendQuery(con,"SET datestyle TO iso")

everything works fine!

So in summary, one has to tell PostgreSQL to return date/time fields in a 
format readable by R.

Greetings and thank you again,

Sandor

Original comment by Sandor...@googlemail.com on 15 Oct 2012 at 9:30

[GoogleCodeExporter]

You are at right position that 
dbSendQuery(con,"SET datestyle TO iso")
solves the problem.
However the default is the ISO format according to the postgresql manual.
http://www.postgresql.org/docs/9.2/static/datatype-datetime.html#DATATYPE-DATETI
ME-OUTPUT

> So in summary, one has to tell PostgreSQL to return date/time fields in a 
format readable by R.

So, this should read one has to tell PostgreSQL to use standard format for the 
communication if the server is intentionally set to return other local format.

You wrote specifically:
1. PostgreSQL Server 9.1 on Windows Server 2003 R2, datestyle = 'German'

DMY and MDY is not distiguishable from the data by itself. Thus, I do strongly 
recommend sticking to ISO format. 

The manual says:
The date/time style can be selected by the user using the SET datestyle 
command, the DateStyle parameter in the postgresql.conf configuration file, or 
the PGDATESTYLE environment variable on the server or client.

So, from the client side, the PGDATESTYLE may affect the behavior, but the 
LC_TIME is unlikely to affect.  

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 17 Oct 2012 at 1:20

[GoogleCodeExporter]

As of r242,
dbSendQuery(con,"SET datestyle TO iso")
is issued at the connection unless otherwise specified.
(Default is to force to ISO conversion. As this does affect query like 
date_field::text, this can be suppressed with an option.)

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 22 Oct 2012 at 7:23

• Changed state: Fixed