Rounding sub-hourly UTC dates up to hourly local time dates in half-hour time zones (i.e. America/St_Johns) is incorrect for the dates that round up through the DST change in the Fall

[B-Nilson]

Goal:

Convert 10-minute UTC observations to hourly local time for non standard timezones (i.e. UTC -0230)

Process tried:

10 minute UTC dates around DST change -> convert to local time using with_tz("America/St_Johns") -> round upwards to hourly using ceiling_date("hours") _> convert back to UTC using with_tz("UTC") to test

Issue:

• 2019-11-03 01:10 -0230 (2019-11-03 03:40 UTC) →
• rounded to 02:00 →
• interpreted as 02:00 -0330 (which is 2019-11-03 05:30 UTC, not 2019-11-03 04:30 UTC as expected)

System:

lubridate version 1.9.3, R version 4.4.1

Reprex:

input <- data.frame(
  date_utc = lubridate::ymd_h("2019-11-03 05", tz = "UTC") +
    lubridate::minutes((-15:3) * 10)
) |> 
  dplyr::mutate(
    date_local = date_utc |> lubridate::with_tz("America/St_Johns"),
    utc_offset = format(date_local, "%z")
  )

input |>
  dplyr::mutate(
    date_local_ceiling = date_local |>
      lubridate::ceiling_date("hours"),
    utc_offset_ceiling = format(date_local_ceiling, "%z"),
    date_ceiling_as_utc = date_local_ceiling |>
      lubridate::with_tz("UTC"),
    converted_utc_delta = difftime(date_ceiling_as_utc, date_utc, units = "mins") # should range from 50 mins to 0 mins
  ) 

Output:

              date_utc          date_local utc_offset  date_local_ceiling utc_offset_ceiling date_ceiling_as_utc converted_utc_delta
1  2019-11-03 02:30:00 2019-11-03 00:00:00      -0230 2019-11-03 00:00:00              -0230 2019-11-03 02:30:00              0 mins
2  2019-11-03 02:40:00 2019-11-03 00:10:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             50 mins
3  2019-11-03 02:50:00 2019-11-03 00:20:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             40 mins
4  2019-11-03 03:00:00 2019-11-03 00:30:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             30 mins
5  2019-11-03 03:10:00 2019-11-03 00:40:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             20 mins
6  2019-11-03 03:20:00 2019-11-03 00:50:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             10 mins
7  2019-11-03 03:30:00 2019-11-03 01:00:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00              0 mins
8  2019-11-03 03:40:00 2019-11-03 01:10:00      -0230 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00            110 mins
9  2019-11-03 03:50:00 2019-11-03 01:20:00      -0230 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00            100 mins
10 2019-11-03 04:00:00 2019-11-03 01:30:00      -0230 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             90 mins
11 2019-11-03 04:10:00 2019-11-03 01:40:00      -0230 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             80 mins
12 2019-11-03 04:20:00 2019-11-03 01:50:00      -0230 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             70 mins
13 2019-11-03 04:30:00 2019-11-03 01:00:00      -0330 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00            -60 mins
14 2019-11-03 04:40:00 2019-11-03 01:10:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             50 mins
15 2019-11-03 04:50:00 2019-11-03 01:20:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             40 mins
16 2019-11-03 05:00:00 2019-11-03 01:30:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             30 mins
17 2019-11-03 05:10:00 2019-11-03 01:40:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             20 mins
18 2019-11-03 05:20:00 2019-11-03 01:50:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             10 mins
19 2019-11-03 05:30:00 2019-11-03 02:00:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00              0 mins

[B-Nilson]

I made a bandaid fix for those with the same issue:

# Splits a '%z' offset string into hours/minutes and adds or subtracts from dates
add_utc_offset <- function(dates, offset_string, negate = FALSE) {
  offset_hours <- offset_string |>
    stringr::str_extract("^[-,+]?\\d\\d") |>
    as.numeric()
  offset_mins <- offset_string |>
    stringr::str_extract("\\d\\d$") |> 
    as.numeric()
  if(offset_hours < 0) offset_mins = -offset_mins

  if (negate) {
    offset_hours <- -offset_hours
    offset_mins <- -offset_mins 
  }

  dates + lubridate::hours(offset_hours) + lubridate::minutes(offset_mins)
}

ceiling_date_local <- function(date_utc, local_tz, unit = "hours"){
  # Use a consistent offset ignoring DST changes 
  utc_offset_1 <- date_utc[1] |> 
    lubridate::with_tz(local_tz) |> 
    format("%z")
  date_utc |> 
    # mock up local time by adding our utc offset (R still thinks is UTC so no DST)
    add_utc_offset(utc_offset_1) |> 
    # take ceiling without hitting DST change issue 
    lubridate::ceiling_date(unit) |> 
    # revert back to UTC time from mock local time
    add_utc_offset(utc_offset_1, negate = TRUE) |>
    # now properly convert to local time, with DST changes considered
    lubridate::with_tz(local_tz)
}

input |>
  dplyr::mutate(
    date_local_ceiling = date_utc |> 
      ceiling_date_local("America/St_Johns", "hours"),
    # test if that worked
    utc_offset_ceiling = format(date_local_ceiling, "%z"),
    date_ceiling_as_utc = date_local_ceiling |>
      lubridate::with_tz("UTC"),
    converted_utc_delta = date_ceiling_as_utc |>
      difftime(date_utc, units = "mins") # should range from 50 mins to 0 mins
  )

Output:

              date_utc          date_local utc_offset  date_local_ceiling utc_offset_ceiling date_ceiling_as_utc converted_utc_delta
1  2019-11-03 02:30:00 2019-11-03 00:00:00      -0230 2019-11-03 00:00:00              -0230 2019-11-03 02:30:00              0 mins
2  2019-11-03 02:40:00 2019-11-03 00:10:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             50 mins
3  2019-11-03 02:50:00 2019-11-03 00:20:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             40 mins
4  2019-11-03 03:00:00 2019-11-03 00:30:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             30 mins
5  2019-11-03 03:10:00 2019-11-03 00:40:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             20 mins
6  2019-11-03 03:20:00 2019-11-03 00:50:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00             10 mins
7  2019-11-03 03:30:00 2019-11-03 01:00:00      -0230 2019-11-03 01:00:00              -0230 2019-11-03 03:30:00              0 mins
8  2019-11-03 03:40:00 2019-11-03 01:10:00      -0230 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00             50 mins
9  2019-11-03 03:50:00 2019-11-03 01:20:00      -0230 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00             40 mins
10 2019-11-03 04:00:00 2019-11-03 01:30:00      -0230 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00             30 mins
11 2019-11-03 04:10:00 2019-11-03 01:40:00      -0230 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00             20 mins
12 2019-11-03 04:20:00 2019-11-03 01:50:00      -0230 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00             10 mins
13 2019-11-03 04:30:00 2019-11-03 01:00:00      -0330 2019-11-03 01:00:00              -0330 2019-11-03 04:30:00              0 mins
14 2019-11-03 04:40:00 2019-11-03 01:10:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             50 mins
15 2019-11-03 04:50:00 2019-11-03 01:20:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             40 mins
16 2019-11-03 05:00:00 2019-11-03 01:30:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             30 mins
17 2019-11-03 05:10:00 2019-11-03 01:40:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             20 mins
18 2019-11-03 05:20:00 2019-11-03 01:50:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00             10 mins
19 2019-11-03 05:30:00 2019-11-03 02:00:00      -0330 2019-11-03 02:00:00              -0330 2019-11-03 05:30:00              0 mins