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!
Usage
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>
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
}