Reads time-series data from an MS Access database table and returns a data frame containing the data. In the data frame the timestamp column contains the timestamps as they are converted to by R from (text versions of) the original timestamps read from MS ACCESS. As this conversion may fail (e.g. the time information gets lost when transferring timestamps from large data sets between R and MS Access) this function may return different pieces of information on the timestamp in forms of additional columns, preceding the timestamp column, in the result data frame. Per default, eleven additional columns are returned: 1. <ts>_txt (timestamp as text), 2. <ts>_Date (date only), 3. <ts>_dSince18991230 (number of days since 1899-12-30), 4. <ts>_secInDay (number of seconds within the day), 5. <ts>_minInDay (number of minutes within the day), 6. <ts>_year (year), 7. <ts>_month (number of month), 8. <ts>_day (number of day within the month), 9. <ts>_h (hours within day), 10. <ts>_min (minutes within hour), 11. <ts>_s (seconds within minute) where in each case <ts> is the name of the timestamp field.
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.
- xTsFields
Extra timestamp fields to be selected. Vector containing numbers between 1 and 11, where each number represents a type of date/time information as described for function
hsSqlExTsFields.- 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
Value
data frame containing the requested data (timestamp and value columns) and additional columns preceding the timestamp column containing different pieces of information on the timestamp.
Details
This function is called internally by the higher-level function
hsMdbTimeSeries that reconstructs the correct timestamps from
the different pieces of timestamp information and provides them in forms of
POSIXct objects in UTC timezone.
Use hsMdbTimeSeries instead if you do not want to care about
any timestamp conversion problems!
Examples
if (FALSE) {
## Get flow time series of 24 of July 2011 from tbl_Hyd in example database
## Additionally to the timestamp that is created by R, return the date only
## (timestamp info id = 2) and the number of minutes within the day
## (timestamp info id = 5).
setCurrentSqlDialect("msaccess")
if (.Platform$OS.type == "windows") {
ts <- hsGetTimeSeries(
mdb = xmdb(),
tbl = "tbl_Hyd",
tsField = "Zeitst",
fields = c("Q", "v"),
minDate = "2011-08-24",
maxDate = "2011-08-25",
xTsFields = c(2, 5),
dbg = TRUE
)
## Show the last records of the returned dataset.
tail(ts)
}
## Output:
# Zeitst_Date Zeitst_minInDay Zeitst Q v
# 1435 2011-08-24 1435 2011-08-24 23:55:00 0.638 0.281
# 1436 2011-08-24 1436 2011-08-24 23:56:00 0.601 0.265
# 1437 2011-08-24 1437 2011-08-24 23:57:00 0.564 0.249
# 1438 2011-08-24 1438 2011-08-24 23:58:00 0.536 0.237
# 1439 2011-08-24 1439 2011-08-24 23:59:00 0.504 0.223
# 1440 2011-08-25 0 2011-08-25 00:00:00 0.483 0.214
}