unexpected behaviour with FILTER on xsd:date

user_1901965 0
Viewed 465

How can I filter the count of a result (occurrences of strikes) to a particular year (1970)? My solution provides unexpected results. In the query I have written down alternatives I tried and their outcome.

Solutions mentioned by others ([1], [2]) did not solve the issue.

The endpoint is: https://api.druid.datalegend.net/datasets/rlzijdeman/ClariahTech2017/containers/clariahTech2017/sparql

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gg: <http://www.gemeentegeschiedenis.nl/gg-schema#>
PREFIX strikes: <https://iisg.amsterdam/vocab/>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?muni ?sdate (COUNT(?muni) as ?muniCount)  
WHERE {
  ?strike strikes:place ?splace .
  ?strike strikes:date ?sdate .
  ?muni rdf:type gg:Municipality .
  ?muni rdfs:label ?ggplace . 
  FILTER regex(?splace, ?ggplace)

  ### TASK: Filter results above to strikes in 1970 only

  # solution 1: extract year and FILTER on 1970
  # FILTER ( year(?sdate) = 1970 )
  ### Virtuoso 22003 Error SR586: Incomplete RDF box as argument 0 for year().

  # solution 2: filter on ?sdate
  # FILTER ( ?sdate >= '1970-01-01'^^xsd:date && ?sdate <= '1970-12-31'^^xsd:date )
  ### Virtuoso 2201B Error SR098: regexp error at '? [Arnhem ( Gelderland )]' column 0 (nothing to repeat)
  ####### Why? This was no problem under solution 1 ?!
  ####### Also: note that each of these works seperately, but not together(!):
  # FILTER ( ?sdate >= '1970-01-01'^^xsd:date )
  # FILTER ( ?sdate <= '1970-12-31'^^xsd:date )

} 
LIMIT 10
virtuoso sparql xsd
1 Answers

Regarding "Solution 1":

The function year takes as input a literal of type xsd:dateTime - your data only contains xsd:date and xsd:gYearMonth literals. That'S why the cast probably fails.

Regarding "Solution 2":

Maybe a bug in Virtuoso. But in general, I'm not sure why you need REGEX here. If you just want to get rid of the language tags for comparison, use str function. It's also much faster:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gg: <http://www.gemeentegeschiedenis.nl/gg-schema#>
PREFIX strikes: <https://iisg.amsterdam/vocab/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT * {
  ?strike strikes:place ?splace .
  ?strike strikes:date ?sdate .
  ?muni rdf:type gg:Municipality .
  ?muni rdfs:label ?ggplace . 
  FILTER (?sdate >= '1970-01-01'^^xsd:date && ?sdate < '1971-01-01'^^xsd:date)
  FILTER(str(?splace) = str(?ggplace)) 
} 
LIMIT 10

Another thing that I find strange in your query, shouldn't you count the strikes instead of the municipality itself? I mean, as far as I understand, you want to get the number of strikes per municipality for a particular date( correct me if I'm wrong). If so, the query should look like this:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gg: <http://www.gemeentegeschiedenis.nl/gg-schema#>
PREFIX strikes: <https://iisg.amsterdam/vocab/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?muni (COUNT(?strike) as ?strikes) {
  ?strike strikes:place ?splace .
  ?strike strikes:date ?sdate .
  ?muni rdf:type gg:Municipality .
  ?muni rdfs:label ?ggplace . 
  FILTER (?sdate >= '1970-01-01'^^xsd:date && ?sdate < '1971-01-01'^^xsd:date)
  FILTER(str(?splace) = str(?ggplace)) 
} 
GROUP BY ?muni
LIMIT 10

In addition, getting the ?sdate doesn't make sense if there are multiple strikes, right? Unless you want to get the dates of all strikes like this:

PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX gg: <http://www.gemeentegeschiedenis.nl/gg-schema#>
PREFIX strikes: <https://iisg.amsterdam/vocab/>
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>

SELECT ?muni (COUNT(?strike) as ?strikes) (GROUP_CONCAT(?sdate; separator = ";") as ?s_dates) {
  ?strike strikes:place ?splace .
  ?strike strikes:date ?sdate .
  ?muni rdf:type gg:Municipality .
  ?muni rdfs:label ?ggplace . 
  FILTER (?sdate >= '1970-01-01'^^xsd:date && ?sdate < '1971-01-01'^^xsd:date)
  FILTER(str(?splace) = str(?ggplace)) 
} 
GROUP BY ?muni
LIMIT 10

Minor comment

I also tried to cast to xsd:dateTime first and then select the year:

FILTER (year(xsd:dateTime(?sdate)) = 1970)

Funnily, this failed because of 29.2. :D :

Virtuoso 22007 Error DT006: Cannot convert 1911-02-29 to datetime : Too many days (29, the month has only 28)

Not sure, if the value 28 for February is hardcoded in Virtuoso, or whether it's bound to leap years - at least this would make sense as 1911 wasn't a leap year (1911 is not divisible by 4, thus, it's a common year)

user_47443590
Related