Issue in time span filter - SDMXRI-1938
The time period end filter is not correctly taken into account:
(Probably thanks to #164 (closed) the pre-formatted mapping is always present) the behaviour is now the same (faulty one) in all tested environments:
- https://nsi-demo-stable.siscc.org/rest/data/OECD,DF_AIR_EMISSIONS,2.1/..TOT?startPeriod=2014&endPeriod=2016&dimensionAtObservation=AllDimensions only returns data for 2014 and 2015, but not for 2016
- https://nsi-qa-reset.siscc.org/rest/data/OECD,DF_AIR_EMISSIONS,2.1/..TOT?startPeriod=2014&endPeriod=2016&dimensionAtObservation=AllDimensions only returns data for 2014 and 2015, but not for 2016
- https://nsi-qa-stable.siscc.org/rest/data/OECD,DF_AIR_EMISSIONS,2.1/..TOT?startPeriod=2014&endPeriod=2016&dimensionAtObservation=AllDimensions only returns data for 2014 and 2015, but not for 2016
There is thus an issue in the comparison of the URL-parameter endPeriod
and the value in the DB table column PERIOD_END
because simple inbuilt type conversions cannot be used.
The following approach should be applied (all assuming pre-formatted period start/end columns are used because this is what we are always doing in .Stat):
Case 1: Only low-frequency time periods are used (PERIOD_START and PERIOD_END columns are DATE typed)
TIME_PERIOD | REPORTING_YEAR _START_DAY |
PERIOD_START | PERIOD_END | URL startPeriod | computed startPeriod | URL endPeriod | computed endPeriod |
---|---|---|---|---|---|---|---|
2016 | 2016-01-01 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 | |
2016-Q4 | 2016-10-01 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 | |
2016-Q2 | "--07-01" | 2016-10-01 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 |
2016-12 | 2016-12-01 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 | |
2016-12-31 | 2016-12-31 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 | |
2012-01-01/P5Y | 2012-01-01 | 2016-12-31 | 2014 | 2014-01-01 | 2016 | 2016-12-31 |
--> The URL startPeriod and endPeriod parameters are to be converted into DATE type instead of current DATETIME type and the URL endPeriod needs a proper calculation/mapping to assure a correct comparison!
SUPPORT_DATETIME
annotation is set (PERIOD_START and PERIOD_END columns are DATETIME typed)
Case 2: High-frequency time periods are also allowed when the TIME_PERIOD | REPORTING_YEAR _START_DAY |
PERIOD_START | PERIOD_END | URL startPeriod | computed startPeriod | URL endPeriod | computed endPeriod |
---|---|---|---|---|---|---|---|
2016 | 2016-01-01T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-Q4 | 2016-10-01T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-Q2 | "--07-01" | 2016-10-01T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 |
2016-12 | 2016-12-01T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-12-31 | 2016-12-31T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-12-31T23 | 2016-12-31T23:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-12-31T23:59 | 2016-12-31T23:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2016-12-31T23:59:59 | 2016-12-31T23:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 | |
2012-01-01T00:00:00/P5Y | 2012-01-01T00:00:00 | 2016-12-31T23:59:59 | 2014 | 2014-01-01T00:00:00 | 2016 | 2016-12-31T23:59:59 |
--> The URL endPeriod needs a proper calculation/mapping to assure a correct comparison!
Note that the conversions of the PERIOD_START and PERIOD_END columns also respects the REPORTING_YEAR_START_DAY attribute as one example is given in the tables (also see dotstatsuite-core-common#113 (closed))
Note: This dataflow https://nsi-demo-stable.siscc.org/rest/data/OECD,AIR_EMISSIONS_DF,1.0/A.AUS.SOX.?startPeriod=2016&endPeriod=2017&dimensionAtObservation=AllDimensions seems to work correctly.
- eurostat's ticket => https://citnet.tech.ec.europa.eu/CITnet/jira/browse/SDMXRI-1938
- eurostat's pull-request => https://citnet.tech.ec.europa.eu/CITnet/stash/projects/SDMXRI/repos/sdmxsource.net/pull-requests/127/overview