SPARQL Date Arithmetic Examples Collection

The following collection presents examples of SPARQL Date arithmetic calculations.

Sample listing of musicians that includes age at time of death

SELECT ?s ?genre ?died ?born 
       ( bif:datediff( 'year', xsd:dateTime( str(?born) ), xsd:dateTime( str(?died) ) ) ) AS ?age
WHERE 
  { 
    {
      SELECT DISTINCT ?s ?genre ?died ?born 
      FROM <http://dbpedia.org> 
      WHERE 
        { 
          ?s a <http://dbpedia.org/ontology/MusicalArtist> ;
                <http://dbpedia.org/ontology/genre> ?genre ;
             <http://dbpedia.org/ontology/deathDate> ?died ;
             <http://dbpedia.org/ontology/birthDate> ?born .
        }
      LIMIT 20 
    }
  }

View online the results of executing this query.

Sample listing of musicians (includes use of IF for data cleansing) that includes age at time of death

SELECT ?person ?genre ?died ?born
       ( 
         IF 
           (
             ( datatype (?born) in (xsd:dateTime, xsd:date) ) 
             and
             ( datatype (?died) in (xsd:dateTime, xsd:date) ),
             bif:datediff( 'year', xsd:dateTime( str(?born) ), xsd:dateTime( str(?died) ) ),
             "error" 
           ) 
       ) AS ?age
WHERE 
  { 
    {
      SELECT DISTINCT ?person ?genre ?died ?born 
      FROM <http://dbpedia.org> 
      WHERE 
        { 
          ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                     <http://dbpedia.org/ontology/genre> ?genre ;
                  <http://dbpedia.org/ontology/deathDate> ?died ;
                  <http://dbpedia.org/ontology/birthDate> ?born .
        }      
      ORDER BY DESC ( <LONG::IRI_RANK> (?person) ) 
      LIMIT 100  
    }
  }  

View online the results of executing this query.

Sample listing of musicians that includes birth date, death date, and age ordered by musicians entity rank

SELECT DISTINCT ?person ?plabel ?genre ?glabel ?died 
      ?born ( <LONG::IRI_RANK> (?person) ) as ?rank
      ( 
        IF 
          (
            ( datatype (?born) in (xsd:dateTime, xsd:date) ) 
            and
            ( datatype (?died) in (xsd:dateTime, xsd:date) ),
            bif:datediff( 'year', xsd:dateTime( str(?born) ), xsd:dateTime( str(?died) ) ),
            "error" 
          ) 
       ) AS ?age
WHERE 
  { 
    {
      SELECT DISTINCT ?person ?plabel ?genre ?glabel ?died ?born 
      FROM <http://dbpedia.org> 
      WHERE 
        { 
          ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                     <http://dbpedia.org/ontology/genre> ?genre ;
                  <http://dbpedia.org/ontology/deathDate> ?died ;
                                             rdfs:label ?plabel ;
                  <http://dbpedia.org/ontology/birthDate> ?born .
                                      ?genre rdfs:label ?glabel .
          FILTER (lang(?plabel) = "en")
          FILTER (lang(?glabel) = "en")
        }
      ORDER BY DESC ( <LONG::IRI_RANK> (?person) ) 
      LIMIT 100  
    }
  }  

View online the results of executing this query.

Sample listing of musicians that includes average age at time of death, by genre

SELECT ?genre, (avg(?age)) AS ?avg
WHERE 
  {
    {
      SELECT DISTINCT ?genre ?person 
             (bif:datediff( 'year', xsd:dateTime( str(?born) ), xsd:dateTime( str(?died)))) as ?age
      WHERE 
        { 
          {
            SELECT DISTINCT ?person ?genre ?died ?born 
            FROM <http://dbpedia.org> 
            WHERE 
              { 
                ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                           <http://dbpedia.org/ontology/genre> ?genre ;
                        <http://dbpedia.org/ontology/deathDate> ?died ;
                        <http://dbpedia.org/ontology/birthDate> ?born .
                FILTER ( datatype (?born) IN (xsd:dateTime, xsd:date) ) 
                FILTER ( datatype (?died) IN (xsd:dateTime, xsd:date) )       
              }      
          }
        }
    }
  }
GROUP BY (?genre)
ORDER BY DESC (?avg)
LIMIT 100

View online the results of executing this query.

Sample listing of musicians that includes average age at time of death with entity rank, by genre

SELECT ?genre, (avg(?age)) AS ?avg,
    ( <LONG::IRI_RANK> (?person) ) as ?rank
WHERE
  {
    {
      SELECT DISTINCT ?genre ?person
        (
          bif:datediff
            (
              'year',
              xsd:dateTime( str(?born) ),
              xsd:dateTime( str(?died) )
            )
        ) as ?age
      WHERE
        {
          {
            SELECT DISTINCT ?person ?genre ?died ?born
            FROM <http://dbpedia.org>
            WHERE
              {
                ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                           <http://dbpedia.org/ontology/genre> ?genre ;
                        <http://dbpedia.org/ontology/deathDate> ?died ;
                        <http://dbpedia.org/ontology/birthDate> ?born .
                FILTER ( datatype (?born) IN (xsd:dateTime, xsd:date) )
                FILTER ( datatype (?died) IN (xsd:dateTime, xsd:date) )
              }
          }
        }
      ORDER BY DESC ( <LONG::IRI_RANK> (?person) )
    }
  }
ORDER BY DESC (?avg) 
LIMIT 100 

View online the results of executing this query.

Sample listing of musicians that includes average age at time of death with entity rank, by genre and Pretty Labels

SELECT ?genre, str(?glabel) as ?genre_names, (avg(?age)) AS ?avg,
    ( <LONG::IRI_RANK> (?person) ) as ?rank 
    
WHERE
  {
    {
      SELECT DISTINCT ?genre ?person ?glabel
        (
          IF 
           (
             ( datatype (?born) in (xsd:dateTime, xsd:date) )
             and
             ( datatype (?died) in (xsd:dateTime, xsd:date) ),
             bif:datediff('year',xsd:dateTime(str(?born)),xsd:dateTime(str(?died))),
             "error" 
           )
        ) as ?age
      WHERE
        {
          {
            SELECT DISTINCT ?person ?genre ?died ?born ?glabel
            FROM <http://dbpedia.org>
            WHERE
              {
                ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                           <http://dbpedia.org/ontology/genre> ?genre ;
                        <http://dbpedia.org/ontology/deathDate> ?died ;
                        <http://dbpedia.org/ontology/birthDate> ?born .
                FILTER ( datatype (?born) IN (xsd:dateTime, xsd:date) )
                FILTER ( datatype (?died) IN (xsd:dateTime, xsd:date) )
                ?genre rdfs:label ?glabel .
                FILTER (?born < ?died) .
                FILTER ( lang(?glabel) = "en" )
              }
          }
        }
      ORDER BY DESC ( <LONG::IRI_RANK> (?person) )
    }
  }
ORDER BY DESC (?avg) 
LIMIT 100

View online the results of executing this query.

Sample listing of musicians that includes Entity Rank, Grouping and Pretty Labels

SELECT DISTINCT ?person str(?plabel) ?genre str(?glabel) 
       ?died ?born ( <LONG::IRI_RANK> (?person) ) as ?rank
       ( 
         IF 
           (
             ( datatype (?born) in (xsd:dateTime, xsd:date) )
             and
             ( datatype (?died) in (xsd:dateTime, xsd:date) ),
             bif:datediff('year',xsd:dateTime(str(?born)),xsd:dateTime(str(?died))),
             "error" 
           ) 
       ) AS ?age
WHERE 
  { 
    {
      SELECT DISTINCT ?person ?plabel ?genre ?glabel ?died ?born 
      FROM <http://dbpedia.org> 
      WHERE 
        { 
          ?person a <http://dbpedia.org/ontology/MusicalArtist> ;
                     <http://dbpedia.org/ontology/genre> ?genre ;
                  <http://dbpedia.org/ontology/deathDate> ?died ;
                                             rdfs:label ?plabel ;
                  <http://dbpedia.org/ontology/birthDate> ?born .
          ?genre rdfs:label ?glabel .
          FILTER ( lang(?plabel) = "en" )
          FILTER ( lang(?glabel) = "en" )
        }
      ORDER BY DESC ( <LONG::IRI_RANK> (?person) ) 
      LIMIT 100  
    }
  }

View online the results of executing this query.

Related

• Virtuoso Tips and Tricks Collection