I am importing data with a DateTime stamp from Access to R and continue to 'lose' my time value. I have had a similar issue a while back (posted right here) and I had to convert the times to a number before importing. While this was not too difficult, it is a step I would like to avoid. This post is also helpful and suggests the reason might be because of the large number or records. I am currently trying to import over 110k records.
As an FYI this post is very helpful for info on dealing with times in R, but did not provide a specific solution for this issue.
My data in Access (2013) looks like this.
As you can see I have a UTC and local time, both of which have the date and time in the same field.
I used the following code to read in the table and look at the head.
DataConnect <- odbcConnect("MstrMUP")
Temp <- sqlFetch(DataConnect, "TempData_3Nov2014")
head(Temp)
IndID UTCDateTime LocalDateTime Temp
1 MTG_030_A 2013-02-08 2013-02-08 25
2 MTG_030_A 2013-02-08 2013-02-08 26
3 MTG_030_A 2013-02-08 2013-02-08 31
4 MTG_030_A 2013-02-08 2013-02-08 29
5 MTG_030_A 2013-02-09 2013-02-08 39
6 MTG_030_A 2013-02-09 2013-02-08 44
As you can see, the time portion of the DateTime stamp is missing, and I can not seem to locate it using str or as.numeric, both of which suggest the time value is not stored (at least that is how I read it).
> str(Temp)
'data.frame': 110382 obs. of 4 variables:
$ IndID : Factor w/ 17 levels "BHS_034_A","BHS_035_A",..: 13 13 13 13 13 13 13 13 13 13 ...
$ UTCDateTime : POSIXct, format: "2013-02-08" "2013-02-08" ...
$ LocalDateTime: POSIXct, format: "2013-02-08" "2013-02-08" ...
$ Temp : int 25 26 31 29 39 44 42 49 42 38 ...
> head(as.numeric(MTG30$LocalDateTime))
[1] 1360306800 1360306800 1360306800 1360306800 1360306800 1360306800
Because all numeric values are the same, they must all be the same date, and do not include time. Correct...?
The Question: Is this an R issue or Access? Any suggestions on how to import 110k rows of data from Access into R without losing the time portion of a DateTime stamp would be appreciated.
I am sure there is a better method than my earlier work around
oh, I almost forgot, I am running the "Sock it to Me" version of R.
EDIT/ADDITION In response to @Richard Scriven thoughts on unclass
Unfortunatly, no, there is not a sec, min, or time value. All are 0.
> temp <- Temp[1:5,]
> unclass(as.POSIXlt(temp$UTCDateTime))
$sec
[1] 0 0 0 0 0
$min
[1] 0 0 0 0 0
$hour
[1] 0 0 0 0 0
$mday
[1] 8 8 8 8 9
$mon
[1] 1 1 1 1 1
$year
[1] 113 113 113 113 113
$wday
[1] 5 5 5 5 6
$yday
[1] 38 38 38 38 39
$isdst
[1] 0 0 0 0 0
$zone
[1] "MST" "MST" "MST" "MST" "MST"
$gmtoff
[1] -25200 -25200 -25200 -25200 -25200
attr(,"tzone")
[1] "" "MST" "MDT"
Thanks in advance.
