Page History

...

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.

...

SELECT DISTINCT id FROM filters WHERE issubfilter = false

Example TimeSeries queries

The Time series can be queried with or without the Filter ID. An example of a query without using the filter ID is:

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'
AND Value BETWEEN '1.9' AND '2.0'

The same query with the use of a filter ID will be as follows:

SELECT * 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'
AND Value BETWEEN '1.9' AND '2.0'

Note on Time Series Queries:

• All values are in the configured time zone of the JDBC application.
• All unreliable values will not be returned in the query. The complete time step of unreliable values is missing in the returned recordset.

Example TimeSeriesGraphs queries

The Time series can be extracted from the database as a graph (binary obejct) through the Timeseriesgraphs table. Queries with or without the Filter ID can be used, similar to the time series table. An example of a query with the use of a filter ID is:

SELECT * from TimeSeriesgraphs
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'
{nodformat}

By default the graphs have a size of 300 (width) * 200 (height) pixels. In the SQL query the width and height can also be fixed.

SELECT * from TimeSeriesgraphs
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'
AND height = 100 AND width = 150

Miscellaneous

Using a different port number (available 200901)

...