Converting UTC to local time using `with_tz()` give wrong local time

Question

I have a dataframe with UTC with i am trying to convert to local times using the with_tz() function, however its giving some weird results, for example London is being put forward by 8 hours rather than by just 1 (or 2 for summer). I'll include any code and data below if anyone knows why this is?

library(lubridate)
df
localtime                 utc         timezone
NA                2022-06-02 19:00:00              CET
NA                2022-06-02 14:00:00 Europe/Bucharest
NA                2022-06-02 14:30:00              CET
NA                2022-06-02 16:00:00              CET
NA                2022-06-02 18:00:00              CET
NA                2022-06-02 16:30:00    Europe/London
NA                2022-06-02 21:00:00       US/Central

To get the local time I then use;

df$utc = as.POSIXct(df$utc, format="%Y-%m-%d %H:%M:%OS", tz = "UTC")
df = df %>% rowwise() %>% mutate(localtime = with_tz(utc, timezone))

This returns wrong results which I'm not sure why as can be seen here;

    localtime                 utc         timezone
  2022-06-03 03:00:00 2022-06-02 19:00:00              CET
  2022-06-02 22:00:00 2022-06-02 14:00:00 Europe/Bucharest
  2022-06-02 22:30:00 2022-06-02 14:30:00              CET
  2022-06-03 00:00:00 2022-06-02 16:00:00              CET   
  2022-06-03 02:00:00 2022-06-02 18:00:00              CET
  2022-06-03 00:30:00 2022-06-02 16:30:00    Europe/London
  2022-06-03 05:00:00 2022-06-02 21:00:00       US/Central

US/Central has been put forward when it should be back, London is 8 hours ahead when it should be 1.

dput if anyone needs it

> dput(df)
structure(list(localtime = c(NA, NA, NA, NA, NA, NA, NA), utc = c("2022-06-02 19:00:00", 
"2022-06-02 14:00:00", "2022-06-02 14:30:00", "2022-06-02 16:00:00", "2022-06-02 18:00:00", 
"2022-06-02 16:30:00", "2022-06-02 21:00:00"), timezone = c("CET", 
"Europe/Bucharest", "CET", "CET", "CET", "Europe/London", "US/Central")), row.names = 1:7, 
class = "data.frame")

This is more tricky than I'd thought, since "R date-time vectors cannot hold elements with non-uniform time zones" as is explained [here](https://stackoverflow.com/questions/49105472/how-to-convert-whole-column-of-dates-times-from-one-time-zone-to-another-in-r) and in the `lubridate` [documentation](https://search.r-project.org/CRAN/refmans/lubridate/html/force_tz.html). Maybe you can find something in the answers or the referenced questions there that suits you. — king_of_limes, Jul 05 '23 at 12:28
@GregorThomas ah yes sorry I deleted and reposted with tweeks as it looked like it was duplicated question. Ok yeh that might be better then as i was having difficulty with merge (not too familiar with it) so i'll give it a go. Thanks for following that up, much appreciated — Joe, Jul 06 '23 at 13:51

score 1 · Accepted Answer · answered Jul 05 '23 at 12:48

Digging a bit further after my initial comment: Since you cannot save items of different timezones in one date-time vector (and thus one column), you have to save them internally as UTC dates. So if you just want them displayed correctly and be aware of the fact that they are wrong internally, you could do this:

Transform every item with with_tz to their respective timezone
Before storing them in the dataframe, change their timezone to UTC with force_tz without changing the clock time.

library(lubridate)

df <- structure(list(localtime = as.POSIXct(c(0,0,0,0,0,0,0),tz = "UTC"), utc = c("2022-06-02 19:00:00", 
                                                                        "2022-06-02 14:00:00", "2022-06-02 14:30:00", "2022-06-02 16:00:00", "2022-06-02 18:00:00", 
                                                                        "2022-06-02 16:30:00", "2022-06-02 21:00:00"), timezone = c("CET", 
                                                                                                                                    "Europe/Bucharest", "CET", "CET", "CET", "Europe/London", "US/Central")), row.names = 1:7, 
                class = "data.frame")

df$utc <- as.POSIXct(df$utc, format="%Y-%m-%d %H:%M:%OS", tz = "UTC")

for(i in 1:nrow(df)){
  temp_time <- with_tz(df$utc[i],df$timezone[i])
  df$localtime[i] <- force_tz(temp_time,"UTC")
}

This only works if the column is already initialized as POSIXct, otherwise you will get a vector of corresponding numeric values.

> df
            localtime                 utc         timezone
1 2022-06-02 21:00:00 2022-06-02 19:00:00              CET
2 2022-06-02 17:00:00 2022-06-02 14:00:00 Europe/Bucharest
3 2022-06-02 16:30:00 2022-06-02 14:30:00              CET
4 2022-06-02 18:00:00 2022-06-02 16:00:00              CET
5 2022-06-02 20:00:00 2022-06-02 18:00:00              CET
6 2022-06-02 17:30:00 2022-06-02 16:30:00    Europe/London
7 2022-06-02 16:00:00 2022-06-02 21:00:00       US/Central

Ok, my only issue that when run on my data i get the localtime returned in numeric values, any idea how they can be converted back to time? or what i have done wrong in the first place here? — Joe, Jul 05 '23 at 13:04
Don't initialize it with `NA`, but already as a `POSIXct` object, for example `localtime = as.POSIXct(c(0,0,0,0,0,0,0),tz = "UTC")`, which initializes the column with the 1st of January, 1970. Or run `df$localtime <- as.POSIXct(df$localtime,tz = 'UTC')` at the end. — king_of_limes, Jul 05 '23 at 13:07

Converting UTC to local time using `with_tz()` give wrong local time

1 Answers1