CODEWITHSUNDEEP
rxts
mynameisJEFF
mynameisJEFF

Reputation: 4239

R: xts timestamp differ from real data timestamp by 1 millisecond

So I have the following data. 

tt <- structure(list(Timestamp = c("2018-03-01 09:51:59.969", "2018-03-01 09:51:59.969", 
"2018-03-01 09:51:59.970", "2018-03-01 09:51:59.971", "2018-03-01 09:51:59.987", 
"2018-03-01 09:51:59.988"), Mid_Px = c(30755.5, 30755, 30755.5, 
30756, 30756.5, 30756.5)), .Names = c("Timestamp", "Mid_Px"), class = "data.frame", row.names = 85774:85779)

which looks like this:

                    Timestamp  Mid_Px
85774 2018-03-01 09:51:59.969 30755.5
85775 2018-03-01 09:51:59.969 30755.0
85776 2018-03-01 09:51:59.970 30755.5
85777 2018-03-01 09:51:59.971 30756.0
85778 2018-03-01 09:51:59.987 30756.5
85779 2018-03-01 09:51:59.988 30756.5

When I try to create an xts object out of it by using the code below, things start to go bad. 

tt_ts <- strptime(tt[,1],"%Y-%m-%d %H:%M:%OS")
tt_ts
[1] "2018-03-01 09:51:59.969 CST" "2018-03-01 09:51:59.969 CST" "2018-03-01 09:51:59.970 CST" "2018-03-01 09:51:59.971 CST" "2018-03-01 09:51:59.987 CST"
[6] "2018-03-01 09:51:59.988 CST"
xts(x=tt[,c(-1)], order.by=tt_ts)
                           [,1]
2018-03-01 09:51:59.969 30755.5
2018-03-01 09:51:59.969 30755.0
2018-03-01 09:51:59.970 30755.5
2018-03-01 09:51:59.970 30756.0
2018-03-01 09:51:59.986 30756.5
2018-03-01 09:51:59.987 30756.5

Notice that the milliseconds are incorrect in row 4,5 and 6.

What have I done wrong here ? How can I fix it to display the correct timestamp ?

Upvotes: 1

Views: 103

Answers (1)

Ralf Stubner
Ralf Stubner

Reputation: 26823

This is similar to R issue with rounding milliseconds. One simple solution would be adding 0.5 ms as suggested there:

tt_ts <- strptime(tt[,1],"%Y-%m-%d %H:%M:%OS") + 0.0005
xts::xts(x=tt[,c(-1)], order.by=tt_ts)
#                            [,1]
# 2018-03-01 09:51:59.969 30755.5
# 2018-03-01 09:51:59.969 30755.0
# 2018-03-01 09:51:59.970 30755.5
# 2018-03-01 09:51:59.971 30756.0
# 2018-03-01 09:51:59.987 30756.5
# 2018-03-01 09:51:59.988 30756.5

We can see this from a simple example:

st <- strptime("2018-03-01 09:51:59.971", "%Y-%m-%d %H:%M:%OS")
format(st, "%Y-%m-%d %H:%M:%OS3")
#> [1] "2018-03-01 09:51:59.971"
pt <- as.POSIXct(st)
format(pt, "%Y-%m-%d %H:%M:%OS3")
#> [1] "2018-03-01 09:51:59.970"

After conversion to POSIXct the ms is wrong. Increasing the output precision, we see that the floating point number used to represent the time is just below the required value, but R truncates the number instead of rounding it:

format(pt, "%Y-%m-%d %H:%M:%OS6")
#> [1] "2018-03-01 09:51:59.970999"

Shifting by one half of the required precision fixes this.

format(pt + 0.0005, "%Y-%m-%d %H:%M:%OS3")
#> [1] "2018-03-01 09:51:59.971"

Generally, if x is a number with 3 decimal digits, any number within the open range (x - 0.0005, x + 0.0005) would be rounded to x. On truncation, that would still work for those within [x, x + 0.0005). But those within (x - 0.0005, x) would be represented by x - 0.001 as you observed. If we shift the relevant number by 0.0005 before truncation, we are speaking about the range (x, x + 0.001). All these numbers will be truncated to x as wanted.

I am excluding the points x ± 0.0005 since there are different rules for rounding them and the actual floating point number representing the time point will be a lot closer to the desired value than this.

EDIT: Concerning the question in the comments about taking differences: There it should not matter whether you add half a milli-second or not if you add it to both points. Example with a time point that needs adjustment on its own:

st1 <- strptime("2018-03-01 09:51:59.971", "%Y-%m-%d %H:%M:%OS")
format(st1, "%Y-%m-%d %H:%M:%OS3")                              
#> [1] "2018-03-01 09:51:59.970"
pt1 <- as.POSIXct(st1)                                          
format(pt1, "%Y-%m-%d %H:%M:%OS3")                              
#> [1] "2018-03-01 09:51:59.970"
format(pt1 + 0.0005, "%Y-%m-%d %H:%M:%OS3")                     
#> [1] "2018-03-01 09:51:59.971"

And a time point that does not need adjustment:

st2 <- strptime("2018-03-01 09:51:59.969", "%Y-%m-%d %H:%M:%OS")
format(st2, "%Y-%m-%d %H:%M:%OS3")                              
#> [1] "2018-03-01 09:51:59.969"
pt2 <- as.POSIXct(st2)                                          
format(pt2, "%Y-%m-%d %H:%M:%OS3")                              
#> [1] "2018-03-01 09:51:59.969"
format(pt2 + 0.0005, "%Y-%m-%d %H:%M:%OS3")                     
#> [1] "2018-03-01 09:51:59.969"

Difference is the same independent of any adjustment:

difftime(pt1, pt2, "secs")                                      
#> Time difference of 0.001999855 secs
difftime(pt1 + 0.0005, pt2 + 0.0005, "secs")                    
#> Time difference of 0.001999855 secs

Upvotes: 2

Related Questions