rtimezonetibble

timezone conversion from tibble dataframe


I'm downloading a tibble dataframe from the alphavantager R package. I'm trying to convert the timestamp column from the default Eastern timezone to UTC. All of my attempts leave the timestamp unchanged, except for putting the suffix "UTC" at the end in some of the attempts.

library('alphavantager')

tst <- av_get(symbol         = "NKE",
              av_fun         = "TIME_SERIES_INTRADAY",
              interval       = "15min",
              extended_hours = "false",
              month          = "2024-03",
              outputsize     = "full") 

# parsing out 1 day  
tst<-tst[as.Date(tst$timestamp)=='2024-03-01',]

# attempt #1
tst$timestamp <- as.POSIXct(as.integer(tst$timestamp),tz="UTC")

# attempt #2
tst$timestamp <- as.POSIXct(as.integer(tst$timestamp), origin="1970-01-01", tz="UTC")

# attempt #3
tst<-as.data.frame(tst)

tst$timestamp <- as.POSIXct(as.integer(tst$timestamp),tz="UTC")

# attempt #4
tst <- as.list(tst)

tst$timestamp <- as.POSIXct(as.integer(tst$timestamp),tz="UTC")`

# example data
dput(head(tst$timestamp))

# Output of dput
# structure(c(1709285400, 1709286300, 1709287200, 1709288100, 
# 1709289000,1709289900), class = c("POSIXct", "POSIXt"), tzone = 
#"UTC")

enter image description here


Solution

  • It downloads as tz = UTC for me and I can change it to CET. (The source Alphavantager JSON metadata shows EST.)

    You could set the tz to EST as that's what the downloaded time actually reflects, then use with_tz(timestamp, tzone = "UTC") to shift the hours.

    library(alphavantager)
    library(lubridate)
    
    av_api_key("xxx")
    
    tst <- av_get(
      symbol = "NKE",
      av_fun = "TIME_SERIES_INTRADAY",
      interval = "15min",
      extended_hours = "false",
      month = "2024-03",
      outputsize = "full"
    )
    
    # Downloaded as UTC
    tz(tst$timestamp)
    #> [1] "UTC"
    tst$timestamp[1]
    #> [1] "2024-03-01 09:30:00 UTC"
    
    # Change to CET
    tz(tst$timestamp) <- "CET"
    tz(tst$timestamp)
    #> [1] "CET"
    tst$timestamp[1]
    #> [1] "2024-03-01 09:30:00 CET"
    
    # Equivalent to
    x <- as_datetime("2024-03-01 09:30:00")
    x
    #> [1] "2024-03-01 09:30:00 UTC"
    tz(x) <- "CET"
    x
    #> [1] "2024-03-01 09:30:00 CET"
    
    
    # To adjust the actual time
    library(dplyr)
    
    tz(tst$timestamp) <- "EST"
    tst$timestamp[1]
    #> [1] "2024-03-01 09:30:00 EST"
    
    tst2 <- tst |> mutate(timestamp = with_tz(timestamp, tzone = "UTC"))
    tst2$timestamp[1]
    #> [1] "2024-03-01 14:30:00 UTC"
    
    # Metadata
    #> [1] https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol=IBM&interval=5min&outputsize=full&apikey=demo
    

    enter image description here

    Created on 2024-04-25 with reprex v2.1.0