adilapapaya / rpostgresql

Automatically exported from code.google.com/p/rpostgresql
0 stars 0 forks source link

Problems concerning the datetime format while importing long timeseries from Postgresql #44

Closed GoogleCodeExporter closed 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?
1. Load the following time series as test into your postgresql database
http://dl.free.fr/fcmgS9phU
sorry, this is a big file, but it needs to be big to find the bug.

2.
Once you connected to the database to which you loaded the data, make two tests 
in R:
(1) 
datebeg <-  "2012-02-01 00:00:00"    
dateend <-  "2012-03-30 00:00:00"

test_data <- dbGetQuery(con,paste("Select date,data from ecoqu.test_data where 
date >= '",datebeg,"' and date < '",dateend,"' order by date",sep=""))

--> everything should work fine

(2)      
datebeg <-  "2012-02-01 00:00:00"    
dateend <-  "2012-08-30 00:00:00"

test_data <- dbGetQuery(con,paste("Select date,data from ecoqu.test_data where 
date >= '",datebeg,"' and date < '",dateend,"' order by date",sep=""))

--> here you only get the date and not the time for the data column

3.

What is the expected output? What do you see instead?

I expect to see the datetimes (which are timestamp without timezone in 
Postgresql) as: '2012-01-01 00:00:00' 
which I get for test (1), but for test (2) I get '2012-01-01' instead and I 
loose the time information, which I really need.

What version of the product are you using? On what operating system?
postgresql 8.3, R 2.14.1 on windows xp

Please provide any additional information below.

Original issue reported on code.google.com by so...@jankowfsky.de on 28 Sep 2012 at 3:41

GoogleCodeExporter commented 9 years ago
see issue 55.

The trailing 00:00:00 is not printed by default, but absence of
that sequence implies it is 00:00:00.
You can try
format(as.POSIXct("2012-01-01"), "%Y-%m-%d %H:%M:%OS")
to recover string with the tail.

Original comment by tomoa...@kenroku.kanazawa-u.ac.jp on 23 Jul 2013 at 8:09