Re: Date Query Performance

[Damian Steer]

On 11 Sep 2013, at 15:04, Iain Ritchie <iainritc...@gmail.com> wrote:

> Hello,
> 
> I am executing the following count(*) query multiple times for a sliding
> date range i.e. count for 1st September, 2nd September etc. Can anyone
> suggest a more efficient way of doing this since I have to execute the
> query a substantial number of times?
> 
> PREFIX xsd:    <http://www.w3.org/2001/XMLSchema#>
> SELECT (count(*) as ?count) WHERE
> {
> ?x <http://dateTime> ?datetime
> FILTER ( "2013-08-02T00:00:00+01:00"^^xsd:dateTime > ?datetime && ?datetime
>> "2013-08-01T00:00:00+01:00"^^xsd:dateTime)
> }
> 
> Many Thanks.

I'd try something of the form:

SELECT ?date  (count(*) as ?count)  WHERE
{
    ?x <http://dateTime> ?datetime
}
GROUP BY (substr(str(?datetime), 1, 10) as ?date)

Roughly: pick a partitioning function, and count grouped by that. In this case 
I've used the first 10 characters of the datetime to get the day.

You could use and extension function here if that rather ropey 'day' isn't 
quite right.

Damian