0

I'm trying to import a very large data that includes timestamp formatted as "2015-08-31 07:23:25.3" The table has almost 2.5mil rows and when I import it into the dataframe, it loses time.

table1 <- dbReadTable(db_connection, "table_name")

Above is the code I have used. It works fine with another table with the same format timestamp and has 800 rows.

I have used following code to check if anything is hidden... no luck :-(

unclass(as.POSIXlt(table1$timestamp))

Please help!

G5W
  • 36,531
  • 10
  • 47
  • 80
user5813583
  • 133
  • 10

1 Answers1

1

Well consider this:

R> library(anytime)
R> anytime("2015-08-31 07:23:25.3")
[1] "2015-08-31 07:23:25.2 CDT"
R>

Standard rounding error not entirely uncommon on numeric data such as dates. But are you very likely making two mistakes:

Display precision is not stored precision

Witness this:

R> options("digits.secs"=0); Sys.time()
[1] "2017-02-12 19:47:49 CST"
R> options("digits.secs"=6); Sys.time()
[1] "2017-02-12 19:47:53.378328 CST"
R>

Time is always at about microsecond precision with R but your default display may be suppressing subseconds. They are always there:

R> now <- Sys.time()
R> now - trunc(now)
Time difference of 0.722091 secs
R>

Wrong import from Postgresql

I was the original mentor for the RPostgreSQL project during its Google Summer of Code. Postgresql has native datetimes, and so does R. Never ever import times as characters. The parsing issue disappear if you properly typecast your query.

Dirk Eddelbuettel
  • 360,940
  • 56
  • 644
  • 725
  • Thanks for your comment! Can't believe I got a comment from the original mentor for the RPostgreSQL! – user5813583 Feb 13 '17 at 01:58
  • oh by the way.. I literally lose time data! Once imported into dataframe, there's only date left. If I force time format, comes as 2017-02-12 00:00:00.. One table with 800 rows works fine, but not the one with 2.5mil rows. Not sure if it's the size that matters.. Only started using sql on R today and so much to learn! – user5813583 Feb 13 '17 at 01:59