hsGetTimeSeries: Get Time Series With Timestamp Info

In KWB-R/kwb.db: Functions supporting data base access

Get Time Series With Timestamp Info

Description

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.

Usage

hsGetTimeSeries(
  mdb,
  tbl,
  tsField = hsTsField(mdb, tbl),
  fields = "*",
  minDate = NULL,
  maxDate = NULL,
  xTsFields = c(1:11),
  inclLast = TRUE,
  sqlFilter = "TRUE",
  dbg = FALSE
)

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

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!

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.

See Also

hsMdbTimeSeries, hsGetTable, hsSqlExTsFields

Examples

## Not run: 
## 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

## End(Not run)
  

KWB-R/kwb.db documentation built on Oct. 1, 2023, 4:10 a.m.