Datetimes in a vector with varying timezones

tidyverse
1

I found a solution for something but wondering if there's a better one. Scenario: We have timestamps for events and we want to know if the event occurred on Thanksgiving Day. The times are all stored in UTC BUT the people are in different time zones and we know their time zones. How do you know if the event happened on Thanksgiving Day? Here's my solution with simulated data.

options(width=200)
library(tibble)
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union
library(purrr)
library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
library(tidyr)

set.seed(12345)

N <- 40

alltimes <- ymd_hms('2025-11-26 12:00:00', tz = "UTC") +
  seconds(0:(86400 * 3 - 1))
head(alltimes)
#> [1] "2025-11-26 12:00:00 UTC" "2025-11-26 12:00:01 UTC" "2025-11-26 12:00:02 UTC" "2025-11-26 12:00:03 UTC" "2025-11-26 12:00:04 UTC" "2025-11-26 12:00:05 UTC"
tail(alltimes)
#> [1] "2025-11-29 11:59:54 UTC" "2025-11-29 11:59:55 UTC" "2025-11-29 11:59:56 UTC" "2025-11-29 11:59:57 UTC" "2025-11-29 11:59:58 UTC" "2025-11-29 11:59:59 UTC"

sampdat <-
  tibble(
    EventDateTimeUTC = sample(alltimes, N),
    TimeZone = sample(
      c(
        "US/Eastern",
        "US/Central",
        "US/Mountain",
        "US/Pacific",
        "US/Hawaii",
        "US/Arizona",
        "US/Alaska"
      ),
      N,
      replace = TRUE
    )
  )

sampdat |> count(TimeZone)
#> # A tibble: 7 × 2
#>   TimeZone        n
#>   <chr>       <int>
#> 1 US/Alaska      11
#> 2 US/Arizona      2
#> 3 US/Central      8
#> 4 US/Eastern      3
#> 5 US/Hawaii       4
#> 6 US/Mountain     5
#> 7 US/Pacific      7

sampdat_adj <-
  sampdat |>
  nest(.by = TimeZone) |>
  mutate(
    data = map2(data, TimeZone, function(x, y) {
      x |>
        mutate(
          DateTimeLocal = with_tz(EventDateTimeUTC, y),
          DateLocal = as.Date(DateTimeLocal, tz = y),
          DateUTC = as.Date(EventDateTimeUTC, tz = "UTC")
        )
    })
  ) |>
  unnest(data) |>
  mutate(
    OnThanksgiving = DateLocal == ymd("2025-11-27") # we care if it was Thanksgiving for them!
  )
sampdat_adj |> filter(DateLocal != DateUTC)
#> # A tibble: 14 × 6
#>    TimeZone   EventDateTimeUTC    DateTimeLocal       DateLocal  DateUTC    OnThanksgiving
#>    <chr>      <dttm>              <dttm>              <date>     <date>     <lgl>         
#>  1 US/Alaska  2025-11-27 06:13:30 2025-11-26 23:13:30 2025-11-26 2025-11-27 FALSE         
#>  2 US/Alaska  2025-11-27 05:19:27 2025-11-26 22:19:27 2025-11-26 2025-11-27 FALSE         
#>  3 US/Alaska  2025-11-29 00:33:42 2025-11-28 17:33:42 2025-11-28 2025-11-29 FALSE         
#>  4 US/Alaska  2025-11-29 04:31:03 2025-11-28 21:31:03 2025-11-28 2025-11-29 FALSE         
#>  5 US/Alaska  2025-11-28 01:52:16 2025-11-27 18:52:16 2025-11-27 2025-11-28 TRUE          
#>  6 US/Alaska  2025-11-28 04:42:56 2025-11-27 21:42:56 2025-11-27 2025-11-28 TRUE          
#>  7 US/Alaska  2025-11-28 04:23:40 2025-11-27 21:23:40 2025-11-27 2025-11-28 TRUE          
#>  8 US/Central 2025-11-29 03:48:58 2025-11-28 20:48:58 2025-11-28 2025-11-29 FALSE         
#>  9 US/Central 2025-11-28 03:08:28 2025-11-27 20:08:28 2025-11-27 2025-11-28 TRUE          
#> 10 US/Hawaii  2025-11-29 01:11:47 2025-11-28 18:11:47 2025-11-28 2025-11-29 FALSE         
#> 11 US/Pacific 2025-11-28 00:31:04 2025-11-27 17:31:04 2025-11-27 2025-11-28 TRUE          
#> 12 US/Pacific 2025-11-28 06:15:23 2025-11-27 23:15:23 2025-11-27 2025-11-28 TRUE          
#> 13 US/Pacific 2025-11-28 03:07:46 2025-11-27 20:07:46 2025-11-27 2025-11-28 TRUE          
#> 14 US/Pacific 2025-11-27 05:13:45 2025-11-26 22:13:45 2025-11-26 2025-11-27 FALSE

# What timezone does each date end up being
sampdat_adj |> select(contains("date")) |> map_chr(tz)
#> EventDateTimeUTC    DateTimeLocal        DateLocal          DateUTC 
#>            "UTC"     "US/Arizona"            "UTC"            "UTC"

sampdat_adj |> arrange(DateTimeLocal) |> print(n=N)
#> # A tibble: 40 × 6
#>    TimeZone    EventDateTimeUTC    DateTimeLocal       DateLocal  DateUTC    OnThanksgiving
#>    <chr>       <dttm>              <dttm>              <date>     <date>     <lgl>         
#>  1 US/Eastern  2025-11-26 12:47:27 2025-11-26 05:47:27 2025-11-26 2025-11-26 FALSE         
#>  2 US/Central  2025-11-26 16:42:33 2025-11-26 09:42:33 2025-11-26 2025-11-26 FALSE         
#>  3 US/Pacific  2025-11-26 18:06:47 2025-11-26 11:06:47 2025-11-26 2025-11-26 FALSE         
#>  4 US/Arizona  2025-11-26 19:05:47 2025-11-26 12:05:47 2025-11-26 2025-11-26 FALSE         
#>  5 US/Central  2025-11-26 21:05:18 2025-11-26 14:05:18 2025-11-26 2025-11-26 FALSE         
#>  6 US/Mountain 2025-11-26 23:23:15 2025-11-26 16:23:15 2025-11-26 2025-11-26 FALSE         
#>  7 US/Pacific  2025-11-27 05:13:45 2025-11-26 22:13:45 2025-11-26 2025-11-27 FALSE         
#>  8 US/Alaska   2025-11-27 05:19:27 2025-11-26 22:19:27 2025-11-26 2025-11-27 FALSE         
#>  9 US/Alaska   2025-11-27 06:13:30 2025-11-26 23:13:30 2025-11-26 2025-11-27 FALSE         
#> 10 US/Mountain 2025-11-27 10:04:56 2025-11-27 03:04:56 2025-11-27 2025-11-27 TRUE          
#> 11 US/Alaska   2025-11-27 13:31:53 2025-11-27 06:31:53 2025-11-27 2025-11-27 TRUE          
#> 12 US/Hawaii   2025-11-27 14:01:03 2025-11-27 07:01:03 2025-11-27 2025-11-27 TRUE          
#> 13 US/Central  2025-11-27 14:37:26 2025-11-27 07:37:26 2025-11-27 2025-11-27 TRUE          
#> 14 US/Pacific  2025-11-27 15:01:31 2025-11-27 08:01:31 2025-11-27 2025-11-27 TRUE          
#> 15 US/Central  2025-11-27 15:28:28 2025-11-27 08:28:28 2025-11-27 2025-11-27 TRUE          
#> 16 US/Eastern  2025-11-27 18:07:39 2025-11-27 11:07:39 2025-11-27 2025-11-27 TRUE          
#> 17 US/Central  2025-11-27 18:37:00 2025-11-27 11:37:00 2025-11-27 2025-11-27 TRUE          
#> 18 US/Hawaii   2025-11-27 19:05:01 2025-11-27 12:05:01 2025-11-27 2025-11-27 TRUE          
#> 19 US/Central  2025-11-27 19:58:58 2025-11-27 12:58:58 2025-11-27 2025-11-27 TRUE          
#> 20 US/Mountain 2025-11-27 20:16:51 2025-11-27 13:16:51 2025-11-27 2025-11-27 TRUE          
#> 21 US/Arizona  2025-11-27 22:08:50 2025-11-27 15:08:50 2025-11-27 2025-11-27 TRUE          
#> 22 US/Pacific  2025-11-27 22:39:50 2025-11-27 15:39:50 2025-11-27 2025-11-27 TRUE          
#> 23 US/Pacific  2025-11-28 00:31:04 2025-11-27 17:31:04 2025-11-27 2025-11-28 TRUE          
#> 24 US/Alaska   2025-11-28 01:52:16 2025-11-27 18:52:16 2025-11-27 2025-11-28 TRUE          
#> 25 US/Pacific  2025-11-28 03:07:46 2025-11-27 20:07:46 2025-11-27 2025-11-28 TRUE          
#> 26 US/Central  2025-11-28 03:08:28 2025-11-27 20:08:28 2025-11-27 2025-11-28 TRUE          
#> 27 US/Alaska   2025-11-28 04:23:40 2025-11-27 21:23:40 2025-11-27 2025-11-28 TRUE          
#> 28 US/Alaska   2025-11-28 04:42:56 2025-11-27 21:42:56 2025-11-27 2025-11-28 TRUE          
#> 29 US/Pacific  2025-11-28 06:15:23 2025-11-27 23:15:23 2025-11-27 2025-11-28 TRUE          
#> 30 US/Hawaii   2025-11-28 11:40:35 2025-11-28 04:40:35 2025-11-28 2025-11-28 FALSE         
#> 31 US/Alaska   2025-11-28 18:25:35 2025-11-28 11:25:35 2025-11-28 2025-11-28 FALSE         
#> 32 US/Mountain 2025-11-28 21:03:11 2025-11-28 14:03:11 2025-11-28 2025-11-28 FALSE         
#> 33 US/Alaska   2025-11-28 21:23:13 2025-11-28 14:23:13 2025-11-28 2025-11-28 FALSE         
#> 34 US/Alaska   2025-11-28 21:38:31 2025-11-28 14:38:31 2025-11-28 2025-11-28 FALSE         
#> 35 US/Alaska   2025-11-29 00:33:42 2025-11-28 17:33:42 2025-11-28 2025-11-29 FALSE         
#> 36 US/Hawaii   2025-11-29 01:11:47 2025-11-28 18:11:47 2025-11-28 2025-11-29 FALSE         
#> 37 US/Central  2025-11-29 03:48:58 2025-11-28 20:48:58 2025-11-28 2025-11-29 FALSE         
#> 38 US/Alaska   2025-11-29 04:31:03 2025-11-28 21:31:03 2025-11-28 2025-11-29 FALSE         
#> 39 US/Eastern  2025-11-29 09:40:26 2025-11-29 02:40:26 2025-11-29 2025-11-29 FALSE         
#> 40 US/Mountain 2025-11-29 10:44:41 2025-11-29 03:44:41 2025-11-29 2025-11-29 FALSE

Created on 2026-09-18 with reprex v2.1.1

2

If you'd rather skip the conversion of all timestamps (and nest() and map2()), you could build a lookup table to match 24 hour Thanksgiving Day intervals in UTC to each time zone and then use (right-open) interval join, something like this:

ThanksgivingUTCLookup <- 
  sampdat |> 
  select(TimeZone) |> 
  distinct() |> 
  mutate(
    ThanksgivingStartUTC = ymd("2025-11-27", tz = "UTC") |> force_tzs(tzones = TimeZone),
      ThanksgivingEndUTC = ThanksgivingStartUTC + days(1),
          OnThanksgiving = TRUE
    )

ThanksgivingUTCLookup
#> # A tibble: 7 × 4
#>   TimeZone    ThanksgivingStartUTC ThanksgivingEndUTC  OnThanksgiving
#>   <chr>       <dttm>               <dttm>              <lgl>         
#> 1 US/Arizona  2025-11-27 07:00:00  2025-11-28 07:00:00 TRUE          
#> 2 US/Mountain 2025-11-27 07:00:00  2025-11-28 07:00:00 TRUE          
#> 3 US/Alaska   2025-11-27 09:00:00  2025-11-28 09:00:00 TRUE          
#> 4 US/Central  2025-11-27 06:00:00  2025-11-28 06:00:00 TRUE          
#> 5 US/Hawaii   2025-11-27 10:00:00  2025-11-28 10:00:00 TRUE          
#> 6 US/Pacific  2025-11-27 08:00:00  2025-11-28 08:00:00 TRUE          
#> 7 US/Eastern  2025-11-27 05:00:00  2025-11-28 05:00:00 TRUE

sampdat |> 
  left_join(
    ThanksgivingUTCLookup, 
    # include lower bound, exclude upper bound
    by = join_by(TimeZone, between(EventDateTimeUTC, ThanksgivingStartUTC, ThanksgivingEndUTC, bounds = "[)"))
  ) |> 
  mutate(OnThanksgiving = coalesce(OnThanksgiving, FALSE)) 
#> # A tibble: 40 × 5
#>    EventDateTimeUTC    TimeZone    ThanksgivingStartUTC ThanksgivingEndUTC  OnThanksgiving
#>    <dttm>              <chr>       <dttm>               <dttm>              <lgl>         
#>  1 2025-11-27 22:08:50 US/Arizona  2025-11-27 07:00:00  2025-11-28 07:00:00 TRUE          
#>  2 2025-11-29 10:44:41 US/Mountain NA                   NA                  FALSE         
#>  3 2025-11-28 21:38:31 US/Alaska   NA                   NA                  FALSE         
#>  4 2025-11-27 15:28:28 US/Central  2025-11-27 06:00:00  2025-11-28 06:00:00 TRUE          
#>  5 2025-11-28 18:25:35 US/Alaska   NA                   NA                  FALSE         
#>  6 2025-11-28 21:23:13 US/Alaska   NA                   NA                  FALSE         
#>  7 2025-11-27 06:13:30 US/Alaska   NA                   NA                  FALSE         
#>  8 2025-11-27 14:37:26 US/Central  2025-11-27 06:00:00  2025-11-28 06:00:00 TRUE          
#>  9 2025-11-27 13:31:53 US/Alaska   2025-11-27 09:00:00  2025-11-28 09:00:00 TRUE          
#> 10 2025-11-27 05:19:27 US/Alaska   NA                   NA                  FALSE         
#> # ℹ 30 more rows