Losing times from 'Timestamp without time zone'

[John Payne]

I've run into some strange behavior while loading a medium-large (400K rows) PostgreSQL data set into R, using RPostgreSQL.  The data include date/times in a column with the PostgreSQL data type 'Timestamp without time zone'.  When I load a small subsection of the data set, everything works properly.  When I load the whole data set, the times are lost and the resulting column in R is a POSIXct object that only has the date part of the timestamp (for example, '2014-03-21 08:44:17' becomes '2014-03-21').  I've confirmed that it's not just an issue of how the times are displayed; they really are missing.

I can work around it by exporting the PostgreSQL table to .csv and then importing the .csv file into R, but I wanted to document the behavior in case others are having similar troubles.

Thanks everyone for your work on the library, which has been very useful to me in the past.

[Andrew Newnham]

We have the same problem. From memory the issue occurs when timezones change (e.g. daylight savings), which is perhaps why subsets work for you.

By default now we use a workaround: casting any datetime to a character string (via to_char) and then parsing dates with lubridate::ymd_hms.

[John Payne]

Thanks Andrew, that's useful.  I note an earlier comment that seems to suggest that this Google Groups page has been replaced by the Github issues page, so I'm going to copy the question there.