EplusSql class wraps SQL queries that can retrieve simulation outputs using EnergyPlus SQLite output file.

Details

SQLite output is an optional output format for EnergyPlus. It will be created if there is an object in class Output:SQLite. If the value of field Option in class Output:SQLite is set to "SimpleAndTabular", then database tables related to the tabular reports will be also included.

There are more than 30 tables in the SQLite output file which contains all of the data found in EnergyPlus's tabular output files, standard variable and meter output files, plus a number of reports that are found in the eplusout.eio output file. The full description for SQLite outputs can be found in the EnergyPlus "Output Details and Examples" documentation. Note that all column names of tables returned have been tidied, i.e. "KeyValue" becomes "key_value", "IsMeter" becomes "is_meter" and etc.

EplusSql class makes it possible to directly retrieve simulation results without creating an EplusJob object. EplusJob can only get simulation outputs after the job was successfully run before.

However, it should be noted that, unlike EplusJob, there is no checking on whether the simulation is terminated or completed unsuccessfully or, the parent Idf has been changed since last simulation. This means that you may encounter some problems when retrieve data from an unsuccessful simulation. It is suggested to carefully go through the .err file using read_err() to make sure the output data in the SQLite is correct and reliable.

Usage

epsql <- eplus_sql(sql)
epsql$path()
epsql$path_idf()
epsql$list_table()
epsql$read_table(table)
epsql$report_data_dict()
epsql$report_data(key_value = NULL, name = NULL, year = NULL, tz = "UTC",
                  case = "auto", all = FALSE, wide = FALSE, period = NULL,
                  month = NULL, day = NULL, hour = NULL, minute = NULL,
                  interval = NULL, simulation_days = NULL, day_type = NULL,
                  environment_name = NULL)
epsql$tabular_data(report_name = NULL, report_for = NULL, table_name = NULL,
                   column_name = NULL, row_name = NULL)
epsql$print()
print(epsql)

Basic Info

epsql <- eplus_sql(sql)
epsql$path()
epsql$path_idf()

$path() returns the path of EnergyPlus SQLite file.

$path_idf() returns the IDF file path with same name as the SQLite file in the same folder. NULL is returned if no corresponding IDF is found.

Arguments:

  • epsql: An EplusSQL object.

  • sql: A path to an local EnergyPlus SQLite output file.

Read Tables

epsql$list_table()
epsql$read_table(table)
epsql$report_data_dict()
epsql$report_data(key_value = NULL, name = NULL, year = NULL, tz = "UTC",
                  case = "auto", all = FALSE, wide = FALSE, period = NULL,
                  month = NULL, day = NULL, hour = NULL, minute = NULL,
                  interval = NULL, simulation_days = NULL, day_type = NULL,
                  environment_name = NULL)
epsql$tabular_data(report_name = NULL, report_for = NULL, table_name = NULL,
                   column_name = NULL, row_name = NULL)

$list_table() returns all available table and view names in the SQLite file.

$read_table() takes a valid table name of those from $list_table() and returns that table data in a data.table format.

$report_data_dict() returns a data.table which contains all information about report data. For details on the meaning of each columns, please see "2.20.2.1 ReportDataDictionary Table" in EnergyPlus "Output Details and Examples" documentation.

$report_data() extracts the report data in a data.table using key values, variable names and other specifications. $report_data() can also directly take all or subset output from $report_data_dict() as input, and extract all data specified. The returned column numbers varies depending on all argument.

  • all is FALSE, the returned data.table has 6 columns:

    • case: Simulation case specified using case argument

    • datetime: The date time of simulation result

    • key_value: Key name of the data

    • name: Actual report data name

    • units: The data units

    • value: The data value

  • all is TRUE, besides columns described above, extra columns are also included:

    • month: The month of reported date time

    • day: The day of month of reported date time

    • hour: The hour of reported date time

    • minute: The minute of reported date time

    • dst: Daylight saving time indicator. Possible values: 0 and 1

    • interval: Length of reporting interval

    • simulation_days: Day of simulation

    • day_type: The type of day, e.g. Monday, Tuesday and etc.

    • environment_period_index: The indice of environment.

    • environment_name: A text string identifying the environment.

    • is_meter: Whether report data is a meter data. Possible values: 0 and 1

    • type: Nature of data type with respect to state. Possible values: Sum and Avg

    • index_group: The report group, e.g. Zone, System

    • timestep_type: Type of data timestep. Possible values: Zone and HVAC System

    • reporting_frequency: The reporting frequency of the variable, e.g. HVAC System Timestep, Zone Timestep.

    • schedule_name: Name of the the schedule that controls reporting frequency.

With the datetime column, it is quite straightforward to apply time-series analysis on the simulation output. However, another painful thing is that every simulation run period has its own Day of Week for Start Day. Randomly setting the year may result in a date time series that does not have the same start day of week as specified in the RunPeriod objects.

eplusr provides a simple solution for this. By setting year to NULL, which is the default behavior, eplusr will calculate a year value (from current year backwards) for each run period that compliances with the start day of week restriction.

It is worth noting that EnergyPlus uses 24-hour clock system where 24 is only used to denote midnight at the end of a calendar day. In EnergyPlus output, "00:24:00" with a time interval being 15 mins represents a time period from "00:23:45" to "00:24:00", and similarly "00:15:00" represents a time period from "00:24:00" to "00:15:00" of the next day. This means that if current day is Friday, day of week rule applied in schedule time period "00:23:45" to "00:24:00" (presented as "00:24:00" in the output) is also Friday, but not Saturday. However, if you try to get the day of week of time "00:24:00" in R, you will get Saturday, but not Friday. This introduces inconsistency and may cause problems when doing data analysis considering day of week value.

With wide equals TRUE, $report_data() will format the simulation output in the same way as standard EnergyPlus csv output file. Sometimes this can be useful as there may be existing tools/workflows that depend on this format. When both wide and all are TRUE, columns of runperiod environment names and date time components are also returned, including: environment_period_index", "environment_name, simulation_days, datetime, month, day, hour, minute, day_type.

$tabular_data() extracts the tabular data in a data.table using report, table, column and row name specifications. The returned data.table has 8 columns:

  • index: Tabular data index

  • report_name: The name of the report that the record belongs to

  • report_for: The For text that is associated with the record

  • table_name: The name of the table that the record belongs to

  • column_name: The name of the column that the record belongs to

  • row_name: The name of the row that the record belongs to

  • units: The units of the record

  • value: The value of the record in string format

For convenience, input character arguments matching in $report_data() and $tabular_data() are case-insensitive.

Arguments

  • key_value: A character vector to identify key values of the data. If NULL, all keys of that variable will be returned. key_value can also be data.frame that contains key_value and name columns. In this case, name argument in $report_data() is ignored. All available key_value for current simulation output can be obtained using $report_data_dict(). Default: NULL.

  • name: A character vector to identify names of the data. If NULL, all names of that variable will be returned. If key_value is a data.frame, name is ignored. All available name for current simulation output can be obtained using $report_data_dict(). Default: NULL.

  • year: Year of the date time in column datetime. If NULL, it will calculate a year value that meets the start day of week restriction for each environment. Default: NULL.

  • tz: Time zone of date time in column datetime. Default: "UTC".

  • case: If not NULL, a character column will be added indicates the case of this simulation. If "auto", the name of the IDF file without extension is used.

  • all: If TRUE, extra columns are also included in the returned data.table.

  • wide: If TRUE, the output is formated in the same way as standard EnergyPlus csv output file.

  • period: A Date or POSIXt vector used to specify which time period to return. The year value does not matter and only month, day, hour and minute value will be used when subsetting. If NULL, all time period of data is returned. Default: NULL.

  • month, day, hour, minute: Each is an integer vector for month, day, hour, minute subsetting of datetime column when querying on the SQL database. If NULL, no subsetting is performed on those components. All possible month, day, hour and minute can be obtained using $read_table("Time"). Default: NULL.

  • interval: An integer vector used to specify which interval length of report to extract. If NULL, all interval will be used. Default: NULL.

  • simulation_days: An integer vector to specify which simulation day data to extract. Note that this number resets after warmup and at the beginning of an environment period. All possible simulation_days can be obtained using $read_table("Time"). If NULL, all simulation days will be used. Default: NULL.

  • day_type: A character vector to specify which day type of data to extract. All possible day types are: Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Holiday, SummerDesignDay, WinterDesignDay, CustomDay1, and CustomDay2. All possible values for current simulation output can be obtained using $read_table("Time").

  • environment_name: A character vector to specify which environment data to extract. If NULL, all environment data are returned. Default: NULL. All possible environment_name for current simulation output can be obtained using.

    $read_table("EnvironmentPeriods")
    
  • report_name, report_for, table_name, column_name, row_name: Each is a character vector for subsetting when querying the SQL database. For the meaning of each argument, please see the description above.

Print

epsql$print()
print(epsql)

$print() shows the core information of this EplusSql object, including the path of the EnergyPlus SQLite file, last modified time of the SQLite file and the path of the IDF file with the same name in the same folder.

Arguments

  • epsql: An EplusSQL object.

Examples

if (FALSE) { if (is_avail_eplus(8.8)) { idf_name <- "1ZoneUncontrolled.idf" epw_name <- "USA_CA_San.Francisco.Intl.AP.724940_TMY3.epw" idf_path <- file.path(eplus_config(8.8)$dir, "ExampleFiles", idf_name) epw_path <- file.path(eplus_config(8.8)$dir, "WeatherData", epw_name) # copy to tempdir and run the model idf <- read_idf(idf_path) idf$run(epw_path, tempdir()) # create from local file sql <- eplus_sql(file.path(tempdir(), "1ZoneUncontrolled.sql")) # get sql file path sql$path() # get the parent IDF file path sql$path_idf() # list all tables in the sql file sql$list_table() # read a specific table sql$read_read("Zones") # read report data dictionary sql$report_data_dict() # read report data sql$report_data(name = "EnergyTransfer:Building") # read tabular data sql$tabular_data() } }