R xts: query both date and time

Question

I can't query xts for subsets including both a date and time range. For example:

> head(myxts['2012-01-06 9:30:00/2012-01-06 10:00:00'], 1)
                    Symbol       
2012-01-03 09:30:00 "AA"

> tail(myxts['2012-01-06 9:30:00/2012-01-06 10:00:00'], 1)
                Symbol       
2012-01-06 10:00:00 "AA"

Here the first row is 2012-01-03. Why not 2012-01-06?

EDIT

I've also tried to extract a single date first to a new variable, then query by time. In this case the date timeseries are extracted correctly but the time subset will not work.

e = myxts['2012-01-06']
e['10:00:00::10:20:00'] # returns all rows in '2012-01-06'

EDIT 2

Found this situation where entering '09:45:00' rather than '9:45:00' seems to fix the problem:

> tail(myxts['2011-12-19 9:40:00::2011-12-19 9:45:00'])
                    Symbol DaySec
2011-12-19 16:00:00 "WPI"  "57600"

> tail(myxts['2011-12-19 9:40:00::2011-12-19 09:45:00'])
                    Symbol DaySec  
2011-12-19 09:45:00 "WPI"  "35100"

score 1 · Accepted Answer · answered Feb 21 '12 at 18:08

1

'2012-01-06 9:30:00' is not an ISO-8601 compliant string. Each time component must be two digits (if specified), so you need '2012-01-06 09:30:00'.

answered Feb 21 '12 at 18:08

Joshua Ulrich

173,410
32
338
418

ok, but shouldn't I get "Error in dimnames(x) <- dn : length of 'dimnames' [2] not equal to array extent" if the index doesn't match any series? – Robert Kubrick Feb 21 '12 at 18:16
Yep, this was the problem. *Both* dates and times must be fully specified, including the leading '0'. You can see I was using the wrong time in the first line of code of my question. Is this tricky... – Robert Kubrick Feb 21 '12 at 18:24
@RobertKubrick: the xts/zoo index doesn't have anything to do with dimnames; they're separate attributes. It's not tricky if you follow the documentation (see `?'[.xts'`, which is admittedly not easy to find). – Joshua Ulrich Feb 21 '12 at 18:25
I think if the date/time is malformed the parser should throw an error. How is it better to silently include more series? – Robert Kubrick Feb 22 '12 at 14:31
@RobertKubrick: You're welcome to submit a patch, or you can wait for it to become a priority for someone else to investigate. If you attempt to submit a patch, please test it against the xts dependencies to ensure you don't break anything. – Joshua Ulrich Feb 22 '12 at 14:35
Problem is at line 81 in parse8601.R. A check could be added for length(date) == 8. Also H can be checked <= 23 & M <= 59 & S <= 59. – Robert Kubrick Feb 22 '12 at 14:58
@RobertKubrick: Sorry, I knew it was in `.parseISO8601`. The time-consuming part, in this case, is patching and ensuring you don't break any existing code. – Joshua Ulrich Feb 22 '12 at 15:13

score 0 · Answer 2 · answered Feb 21 '12 at 17:22

Works for me:

R> now <- Sys.time()
R> foo <- xts(1:100, now + (1:100)*30*60)   # hundred half-hour intervals
R> head(foo)
                          [,1]
2012-02-21 11:48:32.37683    1
2012-02-21 12:18:32.37683    2
2012-02-21 12:48:32.37683    3
2012-02-21 13:18:32.37683    4
2012-02-21 13:48:32.37683    5
2012-02-21 14:18:32.37683    6
R> tail(foo)
                          [,1]
2012-02-23 10:48:32.37683   95
2012-02-23 11:18:32.37683   96
2012-02-23 11:48:32.37683   97
2012-02-23 12:18:32.37683   98
2012-02-23 12:48:32.37683   99
2012-02-23 13:18:32.37683  100

Now we can extract from the last hour on Feb 21 til the first on Feb 23:

R> xtract <- foo["2012-02-21 23:00::2012-02-23 01:00"]
R> head(xtract)
                          [,1]
2012-02-21 23:18:32.37683   24
2012-02-21 23:48:32.37683   25
2012-02-22 00:18:32.37683   26
2012-02-22 00:48:32.37683   27
2012-02-22 01:18:32.37683   28
2012-02-22 01:48:32.37683   29
R> tail(xtract)
                          [,1]
2012-02-22 22:18:32.37683   70
2012-02-22 22:48:32.37683   71
2012-02-22 23:18:32.37683   72
2012-02-22 23:48:32.37683   73
2012-02-23 00:18:32.37683   74
2012-02-23 00:48:32.37683   75
R>

I can also extract within a day:

R> xtract <- foo["2012-02-23 02:00::2012-02-23 04:00"]
R> xtract
                          [,1]
2012-02-23 02:18:32.37683   78
2012-02-23 02:48:32.37683   79
2012-02-23 03:18:32.37683   80
2012-02-23 03:48:32.37683   81
R>

Dirk, I still have the problem, but not on all the xts objects I'm using. It must be some edge case, I added a new test to the question clearly showing the issue. — Robert Kubrick, Feb 21 '12 at 18:04

R xts: query both date and time

2 Answers2

Linked