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")

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

^{Created on 2024-04-25 with reprex v2.1.0}