Unexpected results due to inconsistency with date range lengths

[jonbry]

There are multiple functions in PWDGSI that modify the start and end dates of date ranges different. For example, marsFetchRainfallData and marsFetchRainEventData both add a single day to the end date when querying the database:

pwdgsi/R/mars_data_fetch_write_functions.R

Lines 753 to 758 in 63bc69b

	event_query <- paste(paste0("SELECT * FROM ", rainparams$eventtable),
	"WHERE", rainparams$uidvar, "= CAST('", rainsource, "' as int)",
	"AND eventdatastart >= Date('", start_date, "')",
	"AND eventdataend <= Date('", end_date + lubridate::days(1), "');")
	events <- DBI::dbGetQuery(con, event_query)

marsFetchLevelData on the other hand adds a day before and a day after:

pwdgsi/R/mars_data_fetch_write_functions.R

Lines 682 to 684 in 63bc69b

	# Add buffer to requested dates based on DB time zone
	start_date <- lubridate::round_date(start_date) - lubridate::days(1)
	end_date <- lubridate::round_date(end_date) + lubridate::days(1)

While this gives unexpected results (returns a range outside of the requested amount without notification), it is exasperated by functions that join the data or add their own changes to the date range. For example, the marsEventCombinedPlot adds an extra day before and after, which is then passed to marsFetchMonitoringData, which then is passed to marsFetchRainEventData and MarsFetchRainfallData.

pwdgsi/R/mars_plotting_functions.R

Lines 876 to 879 in 63bc69b

	#check for one day on either side of the event date
	event_date %<>% as.POSIXct(format = '%Y-%m-%d')
	start_date <- event_date - 86400
	end_date <- event_date + 86400

This leads to unexpected results when with marsEventCombinedPlot. The following arguments returns an event for 2024-04-01 even though there was no rain on 3/31:

new_mars <- marsEventCombinedPlot(con = conn_sand,
                             event_date = "2024-03-31",
                             source = "gage",
                             smp_id = "1267-2-1",
                             ow_suffix = "CS2")

But the following shows no rain or event even though there was a rain event on 2024-03-27:

new_mars <- marsEventCombinedPlot(con = conn_sand,
                             event_date = "2024-03-26",
                             source = "gage",
                             smp_id = "1267-2-1",
                             ow_suffix = "CS2")

[jonbry]

Even though we tend to grab inconsistent data ranges, we end up never really using it in functions like marsEventCombinedPlot because we filter based on event_id, which I believe is associated with a specific storm.

pwdgsi/R/mars_plotting_functions.R

Lines 959 to 965 in 63bc69b

	level_data <- mon_data$`Level Data`
	level_data$lvl_lag <- c(diff(level_data$level_ft, 3),0,0,0)
	level_data <- level_data %>% dplyr::mutate(lageql = abs(level_ft - lvl_lag)) %>%
	dplyr::mutate(isevent = !is.na(level_data[,event_col])) %>%
	dplyr::filter(lageql != 0 | isevent) %>%
	#remove columns no longer used
	dplyr::select(-lvl_lag, -lageql,-isevent)

[jonbry]

Per Max:
Issue: During DST, baro downloader gives data with timestamps corresponding to correct dates in local time, not in EST. Given that the downloader now only allows downloads in EST, I'm assuming this is not the intended behavior.

Inputs: Examples that demonstrate this behavior are:

• Selecting 3/1/25-3/8/25 gives expected data
• Selecting 3/1/25-3/9/25 gives data starting at correct time but ending at 23:00 on 3/9/25
• Selecting 3/10/25-3/31/25 gives data starting at 23:00 on 3/9/25 and ending at 23:00 on 3/31/25
• I don't think this is SMP-specific but if it matters, I mainly tested it at 1-1-1.

Likely due to adding a day to the DB query and then goes back an hour when changed to EST

[jonbry]

Likely won't be fixed in PWDGSI but is documented in mars_docs