How to change timezones in xts

Question

I'd like to tell r that my data is in the UTC timezone so that I can then shift them to America/New_York. But when I use indexTZ() it changes the times.

I want the 16:00 UTC hour to become a 12:00 NY hour.

    test = read.zoo(paste0(datadir,"test_.csv"), 
                  index = 1,FUN = as.POSIXct, header = T, sep = ",") 
    test = as.xts(test)
    head(test)


    > QQQ.Open QQQ.High QQQ.Low QQQ.Close QQQ.Volume
    > 
    > 2016-09-10 16:38:00  4665.75  4665.75 4665.75   4665.75          1
    > 2016-09-11 14:13:00  4665.75  4665.75 4665.75   4665.75          1
    > 2016-09-11 22:01:00  4661.25  4667.25 4657.25   4666.75        932
    > 2016-09-11 22:02:00  4666.75  4667.25 4663.25   4665.00        174
    > 2016-09-11 22:03:00  4665.00  4667.00 4665.00   4666.50         66


    indexTZ(test)<- "UTC"
    head(test)

                    QQQ.Open QQQ.High QQQ.Low QQQ.Close QQQ.Volume
2016-09-10 20:38:00  4665.75  4665.75 4665.75   4665.75          1
2016-09-11 18:13:00  4665.75  4665.75 4665.75   4665.75          1
2016-09-12 02:01:00  4661.25  4667.25 4657.25   4666.75        932
2016-09-12 02:02:00  4666.75  4667.25 4663.25   4665.00        174
2016-09-12 02:03:00  4665.00  4667.00 4665.00   4666.50         66
Warning message:
timezone of object (UTC) is different than current timezone ().


> test_dt$hour1 = strftime(test_dt$index, format = "%H", tz = "America/New_York")

> test_dt$hour2 = strftime(test_dt$index, format = "%H", tz = "UTC")

> table(test_dt$hour1)

14 16 22 
 1  1  3 

> table(test_dt$hour2)

02 18 20 
 3  1  1 

Answer 1

Shifting timezones is a little tricky. You need to first step back and realized that the actual stored time is a number (of seconds since the epoch January 1, 1970) representation absolute time. Ie see how I represent same time point ("epoch") as a local time in NY and Moscow:

R> format(as.POSIXct(0,origin="1970-01-01"), tz="UTC")
[1] "1970-01-01"
R> format(as.POSIXct(0,origin="1970-01-01"), tz="America/New_York")
[1] "1969-12-31 19:00:00"
R> format(as.POSIXct(0,origin="1970-01-01"), tz="Europe/Moscow")
[1] "1970-01-01 03:00:00"
R>

Now, the time you have stored were probably parsed as localtime. That is they contain an offset store as the timezone. By changing that you just move relative to the previous time:

R> as.POSIXct("2016-09-10 16:38:00")  # CDT as I am in Chicago
[1] "2016-09-10 16:38:00 CDT"
R> format(as.POSIXct("2016-09-10 16:38:00"), tz="America/New_York")
[1] "2016-09-10 17:38:00"
R> format(as.POSIXct("2016-09-10 16:38:00"), tz="America/Los_Angeles")
[1] "2016-09-10 14:38:00"
R> 

So I understand your question correctly, you need to do two things: 'undo' the local time you have and then move to the desired timezone.

I wrote a helper for this too -- in the RcppCCTZ package. Here is one example for the toTz() function:

R> example(toTz)

toTzR> toTz(Sys.time(), "America/New_York", "Europe/London")
[1] "2016-12-17 01:04:14.184086 CST"

toTzR> # this redoes the 'Armstrong on the moon in NYC and Sydney' example
toTzR> # note that the default print method will print the return object in _your local time_
toTzR> toTz(ISOdatetime(1969,7,20,22,56,0,tz="UTC"), "America/New_York", "Australia/Sydney", verbose=TRUE)
1969-07-20 22:56:00 -0400
1969-07-21 12:56:00 +1000
[1] "1969-07-20 21:56:00 CDT"

toTzR> # whereas explicitly formating for Sydney time does the right thing
toTzR> format(toTz(ISOdatetime(1969,7,20,22,56,0,tz="UTC"), 
toTz+             "America/New_York", "Australia/Sydney", verbose=TRUE), 
toTz+        tz="Australia/Sydney")
1969-07-20 22:56:00 -0400
1969-07-21 12:56:00 +1000
[1] "1969-07-21 12:56:00"

This also shows the added difficulty of ensuring that you print to the desired timezone -- only the second example shows the correct Sydney time as we explicitly told format() to use it.

So to get back to your example:

R> x <- xts(1:2, Sys.time() + 0:1)
R> x
                          [,1]
2016-12-16 20:13:43.29767    1
2016-12-16 20:13:44.29767    2
R> tzone(x) <- "America/New_York"
R> x
                          [,1]
2016-12-16 21:13:43.29767    1
2016-12-16 21:13:44.29767    2
Warning message:
timezone of object (America/New_York) is different than current timezone (). 
R> index(x) <- index(x) - 60*60  # dirty method, last resort
R> x
                          [,1]
2016-12-16 20:13:43.29767    1
2016-12-16 20:13:44.29767    2
Warning message:
timezone of object (America/New_York) is different than current timezone (). 
R> tzone(x)
                TZ 
"America/New_York" 
R> 

So I explicitly changed the numeric value by 60 minutes to account for the fact that I shifted time by an hour (from Chicago, my local time, to New York).

Answer 2

Your data was probably loaded as "America/New_York" time, when you probably should have specified the timezone as "UTC". (You may be able to pass in the timezone argument as a passthrough parameter for FUN=POSIXct.)

Reproducing your original situation:

data <- " 2016-09-10 16:38:00  4665.75  4665.75 4665.75   4665.75          1
2016-09-11 14:13:00  4665.75  4665.75 4665.75   4665.75          1
2016-09-11 22:01:00  4661.25  4667.25 4657.25   4666.75        932
2016-09-11 22:02:00  4666.75  4667.25 4663.25   4665.00        174
2016-09-11 22:03:00  4665.00  4667.00 4665.00   4666.50         66"

data = read.table(text = data,
                  col.names = c("date", "time", "Open" ,  "High", "Low", "Close", "Volume")
                  )
# assumes data is loaded in America/New_York time zone
x_data <- xts(order.by = as.POSIXct(paste(data$date, data$time), tz = "America/New_York"), data[3:NCOL(data)])


x_data
# Open    High     Low   Close Volume
# 2016-09-10 16:38:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 14:13:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 22:01:00 4661.25 4667.25 4657.25 4666.75    932
# 2016-09-11 22:02:00 4666.75 4667.25 4663.25 4665.00    174
# 2016-09-11 22:03:00 4665.00 4667.00 4665.00 4666.50     66

indexTZ(x_data) <- "UTC"

# This reproduces your situation (problem):
head(x_data)
# Open    High     Low   Close Volume
# 2016-09-10 20:38:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 18:13:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-12 02:01:00 4661.25 4667.25 4657.25 4666.75    932
# 2016-09-12 02:02:00 4666.75 4667.25 4663.25 4665.00    174
# 2016-09-12 02:03:00 4665.00 4667.00 4665.00 4666.50     66


# This is what you probably wanted to do. Set the initial timezone to "UTC" when you loaded the data into R and created your `POSIXct` objects.

x_data <- xts(order.by = as.POSIXct(paste(data$date, data$time), tz = "UTC"), data[3:NCOL(data)])
head(x_data)
# Open    High     Low   Close Volume
# 2016-09-10 16:38:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 14:13:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 22:01:00 4661.25 4667.25 4657.25 4666.75    932
# 2016-09-11 22:02:00 4666.75 4667.25 4663.25 4665.00    174
# 2016-09-11 22:03:00 4665.00 4667.00 4665.00 4666.50     66
indexTZ(x_data) <- "America/New_York"
# Now you get your desired outcome:
head(x_data)
# Open    High     Low   Close Volume
# 2016-09-10 12:38:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 10:13:00 4665.75 4665.75 4665.75 4665.75      1
# 2016-09-11 18:01:00 4661.25 4667.25 4657.25 4666.75    932
# 2016-09-11 18:02:00 4666.75 4667.25 4663.25 4665.00    174
# 2016-09-11 18:03:00 4665.00 4667.00 4665.00 4666.50     66

Answer 3

Aha, yes, the answer is to create the xts object with the correct timezone:

e.g.:

S = as.xts(test, tz = "UTC")