QGIS date time UTC and "America/Vancouver" - Date Time Daylight Savings Time zones geopackage standard R

[NewGraphEnvironment]

this is a continuation of https://github.com/NewGraphEnvironment/fish_passage_template_reporting/issues/18

Summary:

• When we burn geopackages with R the time is stored in the geopackage as UTC since that is the geopackage standard.
• When we subsequently view the new geopackage in QGIS we see them in UTC which is inconvenient as we sometimes need to use the times for our work and in some cases the date could even be wrong (off by 7 or 8 hours)
• the field calculator is QGIS is a convenient way to convert a datetime column to the "America/Vancouver" however it is extremely complicated to correctly account for daylight savings time

clocks have moved forward on the second Sunday in March and then moved back on first Sunday of November

• R handles conversions very well and it seems as though the issues we say in the above issue was case specific and does not seem to be happening currently.

So here are the options for if we want to view times in "America/Vancouver" in QGIS after we have reburned a geopackage with R

1. Make a new column in R before reburning that turns the time to a string with the timezone info appended

# this example straddles daylight savings time so captured complex use case of capturing time zone details as strings.

df <- tibble::tibble(
  date_time_vancouver = lubridate::ymd_hms(c(
    "2024-03-10 12:00:00",  # DST starts (PDT)
    "2024-07-01 12:00:00",  # Within DST (PDT)
    "2024-11-03 12:00:00",  # DST ends (PST)
    "2024-12-01 12:00:00"   # Standard Time (PST)
  ), tz = "America/Vancouver")
)

df_freeze <- df |> 
  dplyr::mutate(
    date_time_freeze = format(date_time_vancouver, "%Y-%m-%d %H:%M:%S %Z")
    )

2. Make a new column with the attribute calculator in QGIS and use it only for viewing. NOT ACTUALLY CORRECT so use with grain of salt - but darned close and suitable for data collected from mid march to end of October

CASE 
  WHEN month("date_time_start") >= 3 AND month("date_time_start") <= 11 
       AND NOT (month("date_time_start") = 3 AND day("date_time_start") < 14)
       AND NOT (month("date_time_start") = 11 AND day("date_time_start") >= 7)
  THEN format_date("date_time_start" - make_interval(hours := 7), 'yyyy-MM-dd HH:mm:ss') || ' (PDT)'
  ELSE format_date("date_time_start" - make_interval(hours := 8), 'yyyy-MM-dd HH:mm:ss') || ' (PST)'
END

3. use a python script to convert the date in QGIS. Seems unneccesary but since it is likely possible will put chatgpts first try at it here in case it is useful one day

from datetime import datetime, timedelta

# Function to calculate if a date is in Daylight Saving Time
def is_dst(date):
    year = date.year
    # DST starts: Second Sunday in March
    dst_start = datetime(year, 3, 8) + timedelta(days=(6 - datetime(year, 3, 8).weekday()))
    # DST ends: First Sunday in November
    dst_end = datetime(year, 11, 1) + timedelta(days=(6 - datetime(year, 11, 1).weekday()))
    return dst_start <= date < dst_end

# Adjust time based on DST
def adjust_to_pst_pdt(date_str):
    date = datetime.strptime(date_str, "%Y-%m-%d %H:%M:%S")
    if is_dst(date):
        adjusted_date = date - timedelta(hours=7)
        return adjusted_date.strftime("%Y-%m-%d %H:%M:%S (PDT)")
    else:
        adjusted_date = date - timedelta(hours=8)
        return adjusted_date.strftime("%Y-%m-%d %H:%M:%S (PST)")

# Apply the function to your QGIS field
layer = iface.activeLayer()
field_name = "date_time_start"
new_field_name = "date_time_pst"

# Add a new field for adjusted time
if new_field_name not in [field.name() for field in layer.fields()]:
    layer.dataProvider().addAttributes([QgsField(new_field_name, QVariant.String)])
    layer.updateFields()

# Adjust each row
with edit(layer):
    for feature in layer.getFeatures():
        original_date = feature[field_name]
        adjusted_date = adjust_to_pst_pdt(original_date)
        feature[new_field_name] = adjusted_date
        layer.updateFeature(feature)

Here is the code to repair time when we get changes in R that do not reflect the reality of the actual time. We have not seen this lately as R seems to be serving us the Vancouver time (our system time which is a mix of Pacific Standard Time -PST and Pacific Daylight Time - PDT) correctly after reading in UTC from geopackages. Here is the code to do the repair for completeness anyway. Note that this code is counter intuitive as it is a hack to repair an incorrect time vs the code to correctly transform an accurate time

form_pscis_zone <- form_pscis |> 
  mutate(date_time_start_zone_utc= force_tz(date_time_start, tzone = "America/Vancouver")) |> 
  mutate(date_time_start_zone_pdt = with_tz(date_time_start_zone_utc, tzone = "UTC"))