2

I'm importing data from SQL into R. The data contain a column in the format of DateTime in SQL with dates in the following format: 2014-10-01 13:00:05. However, when I import these data into R using the RODBC package the column in R is formatted to data type POSIXct and displays the column as follows: 2014-10-01 i.e. it doesn't contain the hours, minutes or seconds.

Does anyone know how to import from SQL into R and maintain the hours, minutes and seconds?

Please let me know if I need to add anything to the question to clarify further.

Thanks

Mike

Mike
  • 1,049
  • 5
  • 20
  • 46
  • Did you try incresing the value of the `digits` argument of the `POSIXct` class? – Mr. Mascaro Nov 06 '14 at 15:18
  • No, how do I do that? – Mike Nov 06 '14 at 15:32
  • [This](http://stat.ethz.ch/R-manual/R-devel/library/base/html/DateTimeClasses.html) might help. – Mr. Mascaro Nov 06 '14 at 15:37
  • It depends a little on the db and drivers you are using. The ODBC protocol can get a little fuzzy when converting types (different db's consider different things dates, date-times, etc.) and so RODBC has to do some educated guessing. Try using the `as.is` argument to pull that column in as character and then convert it yourself in R. – joran Nov 06 '14 at 15:50
  • Hi guys, thanks for the replies. I'm still struggling. How do I do the `as.is` argument? My query currently looks like this `sqlQuery(myconn,paste0("SELECT Period FROM stats.HourV2OnDisk WHERE Login = ", clientLogins[j,1]))` – Mike Nov 06 '14 at 16:05
  • That argument is pretty clearly documented. What exactly about the description in the documentation on how to use it is confusing you? – joran Nov 06 '14 at 18:42

3 Answers3

3

I have had problems with the sqlQuery doing this too. An easy fix, if your dataset isn't too large, is the "as.is" option. This works well with sqlFetch

sqlFetch(myconn, "table_name", as.is = c(TRUE,TRUE,TRUE,TRUE))
farmkid
  • 420
  • 2
  • 11
3

This could be due to a mismatch between your machine's local time zone (Sys.timezone()) and the timezone of the data you are retrieving.

RODBC uses as.POSIXct which uses your machine's current time zone by default. If the data contains any time stamps invalid or ambiguous in that timezone weirdstuff might happen. For example transitions to and from daylight savings may cause trouble.

These work fine because both time stamps are well defined in the local timezone and in UTC:

> as.POSIXct(c("2016-01-01 00:00:01","2016-03-26 02:30:00"))
[1] "2016-01-01 00:00:01 CET" "2016-03-26 02:30:00 CET"
> as.POSIXct(c("2016-01-01 00:00:01","2016-03-26 02:30:00"), tz="UTC")
[1] "2016-01-01 00:00:01 UTC" "2016-03-26 02:30:00 UTC"

The first of these silently drops H:M:S on all timestamps because the last time stamp is invalid in the local time zone:

> as.POSIXct(c("2016-01-01 00:00:01","2016-03-27 02:30:00"))
[1] "2016-01-01 CET" "2016-03-27 CET"
> as.POSIXct(c("2016-01-01 00:00:01","2016-03-27 02:30:00"), tz="UTC")
[1] "2016-01-01 00:00:01 UTC" "2016-03-27 02:30:00 UTC"

One solution is to fetch data with as.is, then manually converting to POSIXct specifying the proper time zone. In this case the db contains UTC time stamps:

data <- sqlQuery(mssql,
                 "SELECT timestamp, value FROM table",
                 as.is=c(TRUE, FALSE))
data$timestamp <- as.POSIXct(data$timestamp, tz="UTC")
amhr
  • 41
  • 5
1

I had the same question and came across the following solution:

R: loss of precision when POSIXct binding with datetime (sql server)

You simply need to read the column in as a string:

select convert(nvarchar(24), Dt, 21) as Dt, value from mybus

-Mike

Community
  • 1
  • 1
user2371007
  • 71
  • 2