Page History

...

There are a number of SQL queries that can be used to retrieve data from the database. Only (read-only) statements are supported. Statements must be formatted as:

SELECT [DISTINCT] <select_expr> FROM TABLE_NAME [WHERE <where_condition>] [ORDER BY COLUMN_NAME [ASC

...

 |DESC]]

<select_expr>: (* | <COLUMN_NAME [, COLUMN_NAME, ...|, COLUMN_NAME, ...])

...

<where_condition>: COLUMN_NAME <operator> [AND <where_condition> OR <where_condition> LIKE <where_condition>

...

]

<operator>: (= | <> | < | > <value>) | BETWEEN <value> AND <value>

For the Locations, Parameter and Filters table the SQL Query "Select * from <TableName>" is allowed. For the TimeSeries Table this query will return an error.

A valid query for the TimeSeries Table is as follows:

SELECT * from TimeSeries
WHERE moduleInstanceId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND valueType = 'scalar'
AND time BETWEEN '2008-12-19 12:00:00'  AND '2008-12-23 12:00:00'
AND timeSeriesType = 'external forecasting'
AND timeStep = 'unit=hour multiplier=6'

Or, when using filter id's:

SELECT time, value from TimeSeries
WHERE filterId = 'ImportSHEF'
AND parameterId = 'FMAT'
AND locationId = 'DETO3IL'
AND time BETWEEN '2008-12-19 12:00:00'  AND '2008-12-23 12:00:00'

Note ! When creating a query using the clause time BETWEEN '2007-03-17 13:00:00' AND '2007-04-01 13:00:00', then it is good to realise that the start time is used as system time for retrieving the timeseries data. This could be important when retrieving 'external forecasting' data with an 'externalForecastTime' later than the start time.This will result in no data being returned.

Example Locations queries

...