On 11/09/13 15:18, Damian Steer wrote:
On 11 Sep 2013, at 15:04, Iain Ritchie <[email protected]> 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
You can cast from xsd:dateTime to xsd:date:
GROUP BY (xsd:date(?datetime))
Andy
PS - Minor oddity - it does not preserve the timezone
xsd:date("2013-09-11T16:00:00+01:00"^^xsd:dateTime) ==>
"2013-09-11"^^xsd:date
whether that's a bug or a feature depends on your POV regarding
timezones and xsd:dates (yes - they have an optional timezone so it is
bug really)
