Get Mdb time series in UTC

Source: R/main.R

Reads time-series data from an MS Access database table and returns a data frame containing the data. The name of the timestamp field must be given in tsField and the names of the value fields to be selected from the table must be given in vector fields. Instead of an ODBC channel the name of the database must be given. This function takes care that the timestamps are transferred correctly between MS Access and R by requesting date and time information separately from MS Access and putting both together to a POSIXct object in UTC timezone. This is necessary because with very long data sets the RODBC function sqlQuery (or the function hsSqlQuery that calls this function) may deliver timestamps in which time information is lacking!

hsMdbTimeSeries(
  mdb,
  tbl,
  tsField = hsTsField(mdb, tbl),
  fields = "*",
  minDate = NULL,
  maxDate = NULL,
  resolution = "min",
  inclLast = TRUE,
  sqlFilter = "TRUE",
  dbg = FALSE,
  calcType = 1
)

Arguments

mdb

Full path to MS Access database file (*.mdb)
tbl

Name of table containing the time-series data.
tsField

Name of table field containing the timestamps.
fields

Vector containing names of value fields to be selected from the table. This vector may or may not contain the name of the timetamp field.
minDate

Minimum date (and time) of time interval to be selected in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional.
maxDate

Day following the maximum date of the time interval to be selected, in ISO-Syntax: yyyy-mm-dd [HH:MM:SS], where the part in brackets in optional.
resolution

time resolution: “min” = minutes, “s” = seconds. If time resolution is “min” timestamps are rounded to the next full minute.
inclLast

If TRUE, maxDate will be included in result data set, else excluded.
sqlFilter

additional SQL filter criterion
dbg

if TRUE, debug messages are shown
calcType

for internal use only, do not change!

Value

data.frame with POSIXct timestamp column <strTimestamp> (UTC time zone) and value columns as selected in <strFieldList>

See also

hsGetTimeSeries, hsGetTable

Examples

if (FALSE) {
## Get flow time series of 24 of August 2011 from tbl_Hyd in example database

if (.Platform$OS.type == "windows") {

  ts <- hsMdbTimeSeries(
    xmdb(), "tbl_Hyd", "Zeitst", c("Q", "v"), "2011-08-24", "2011-08-25"
  )

  ## Show the last records of the returned dataset.
  tail(ts)
}

## Output:
#                   Zeitst     Q     v
# 1435 2011-08-24 23:55:00 0.638 0.281
# 1436 2011-08-24 23:56:00 0.601 0.265
# 1437 2011-08-24 23:57:00 0.564 0.249
# 1438 2011-08-24 23:58:00 0.536 0.237
# 1439 2011-08-24 23:59:00 0.504 0.223
# 1440 2011-08-25 00:00:00 0.483 0.214
}