rggplot2lubridateutcdatetime-conversion

How to convert UTC to EET in ggplot2 geom_segment chart?


I'm trying to adjust the UTC time into EET time in a gantt type of chart, where I want to plot some Cron schedulings which are in the UTC time. Here is the code. How can I adjust the time conversion so that the Y-axis is correct in the plot (e.g. AA Startime at 05:00, not 03:00)?

library(jsonlite)
library(dplyr)
library(ggplot2)
library(scales)
library(lubridate)

# JSON data
json_data <- '[
{"SchedulingName": "AA", "cronExpr": "0 3 * * *"},
{"SchedulingName": "AB", "cronExpr": "30 2 * * *"},
{"SchedulingName": "AC", "cronExpr": "30 2 * * *"},
{"SchedulingName": "AD", "cronExpr": "0 6 * * *"},
{"SchedulingName": "ADD", "cronExpr": "30 2 * * *"},
{"SchedulingName": "ADER", "cronExpr": "55 1,7 * * *"},
{"SchedulingName": "ADF", "cronExpr": "9 9 * * *"},
{"SchedulingName": "ERE", "cronExpr": "0 0 * * 0"},
{"SchedulingName": "DFA", "cronExpr": "0 5 * * *"},
{"SchedulingName": "DFD", "cronExpr": "45 6 * * *"},
{"SchedulingName": "GREW", "cronExpr": "30 2 * * *"},
{"SchedulingName": "ASDFA", "cronExpr": "9 9 * * *"},
{"SchedulingName": "AFERE", "cronExpr": "30 4 * * *"},
{"SchedulingName": "DFAARE", "cronExpr": "30 2 * * *"},
{"SchedulingName": "DFARE", "cronExpr": "9 9 * * *"},
{"SchedulingName": "DAFAD", "cronExpr": "30 2 * * *"},
{"SchedulingName": "ADWER", "cronExpr": "0 5 * * *"},
{"SchedulingName": "OASDFAS", "cronExpr": "0 3 * * *"},
{"SchedulingName": "ASDPFAS", "cronExpr": "50 2 * * *"},
{"SchedulingName": "ASDFASDF", "cronExpr": "45 3 * * *"},
{"SchedulingName": "AERED", "cronExpr": "0 22 * * *"},
{"SchedulingName": "ASDFAER", "cronExpr": "30 5,12 * * *"},
{"SchedulingName": "AREWVSD", "cronExpr": "9 9 * * *"},
{"SchedulingName": "SARAER", "cronExpr": "30 2 * * *"},
{"SchedulingName": "QEFHF", "cronExpr": "0 3 * * *"},
{"SchedulingName": "AFEREADF", "cronExpr": "30 3 * * *"},
{"SchedulingName": "UTKY", "cronExpr": "30 6 * * *"},
{"SchedulingName": "IUOU", "cronExpr": "0 5 * * *"},
{"SchedulingName": "KYDF", "cronExpr": "0 7 * * *"},
{"SchedulingName": "DSARAE", "cronExpr": "0 0 * * *"},
{"SchedulingName": "KYRT", "cronExpr": "9 9 * * *"},
{"SchedulingName": "WERRYHN", "cronExpr": "0 1 * * *"},
{"SchedulingName": "FHSUR", "cronExpr": "30 12 * * *"},
{"SchedulingName": "PIU", "cronExpr": "30 23 * * *"},
{"SchedulingName": "ASDGYHBDV", "cronExpr": "0 3 * * *"},
{"SchedulingName": "KYIUY", "cronExpr": "0 19 * * *"},
{"SchedulingName": "IRETH", "cronExpr": "9 9 * * *"},
{"SchedulingName": "JTUT", "cronExpr": "31 23 * * *"},
{"SchedulingName": "HRURJU", "cronExpr": "0 20 * * *"}
]'

# Convert JSON to DataFrame
cron_jobs <- fromJSON(json_data)
head(cron_jobs)

# Process data to extract times and create POSIXct timestamps
cron_jobs <- cron_jobs %>%
  mutate(
    Minute = as.numeric(sub("^(\\d+)\\s.*", "\\1", cronExpr)),
    Hour = as.numeric(sub("^\\d+\\s(\\d+).*", "\\1", cronExpr)),
    StartTime = as.POSIXct(paste("2024-01-01", Hour, Minute), format = "%Y-%m-%d %H %M", tz = "UTC"),
    EndTime = StartTime + 720,  # Add 12 minutes for task duration
    StartTime = as_datetime(StartTime, tz = 'Europe/Helsinki'),
    EndTime = as_datetime(EndTime, tz = "Europe/Helsinki")
  )
head(cron_jobs)

# Sort the factor levels for better visualization
cron_jobs <- cron_jobs %>%
  arrange(SchedulingName) %>%
  mutate(SchedulingName = factor(SchedulingName, levels = rev(unique(SchedulingName))))

# Date range for the Gantt chart
full_day_start <- force_tz(as.POSIXct("2024-01-01 00:00", format = "%Y-%m-%d %H:%M", tz = "UTC"), tzone = "Europe/Helsinki")
full_day_end <- force_tz(as.POSIXct("2024-01-02 00:00", format = "%Y-%m-%d %H:%M", tz = "UTC"), tzone = "Europe/Helsinki")

# Create Gantt chart
ggplot(cron_jobs, aes(x = SchedulingName, y = StartTime, xend = SchedulingName, yend = EndTime)) +
  geom_segment(size = 3, color = "red") +
  coord_flip() +
  scale_y_datetime(
    breaks = seq(full_day_start, full_day_end, by = "1 hour"),
    labels = date_format("%H:%M"),
    limits = c(full_day_start, full_day_end)
  ) +
  labs(title = "Cron Schedule Gantt Chart (EET)", x = "Task", y = "Time") +
  theme_minimal() +
  theme(
    axis.text.x = element_text(angle = 90, hjust = 1)
  )

Solution

  • You need to add a timezone to the date_format statement, which seems to do more than just set the format. Note that date_format has now been superseded by label_time, although either will currently work.

    ggplot(cron_jobs, aes(x = SchedulingName, y = StartTime, xend = SchedulingName, 
                          yend = EndTime)) +
      geom_segment(size = 3, color = "red") +
      coord_flip() +
      scale_y_datetime(
        breaks = seq(full_day_start, full_day_end, by = "1 hour"),
        labels = label_time("%H:%M", tz = "EET"),    # or date_format(...)
        limits = c(full_day_start, full_day_end)
      ) +
      labs(title = "Cron Schedule Gantt Chart (EET)", x = "Task", y = "Time") +
      theme_minimal() +
      theme(
        axis.text.x = element_text(angle = 90, hjust = 1)
      )
    

    enter image description here