Filtering between two dates in SPARQL

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Filtering between two dates in SPARQL

Dipali
Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

Ontology_Tutorial.zip (112K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Lorenz Buehmann

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:


PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                 FILTER (?machineid = <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>)

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement


On 13.10.2017 15:41, Dipali wrote:
Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Dipali
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.



I am using Protege 5.2  version.Installed Plugin versions are:


In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To: [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL
 

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl:
PREFIX rdf:
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema# VTFRYAE8ASYAO1JtUzI=" rel="external" target="_blank">
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
<a class="moz-txt-link-freetext" href="https://mailman.stanford.edu/mailman/listinfo/protege-user VHNTaFRrVjc=" rel="external" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

Ontology_Tutorial.zip (112K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Matthew Horridge-2
Administrator
Hi,

You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.

BIND((year(?date) - 1) as ?startYear)

Cheers,

Matthew


On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:

Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

<36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

<2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To: [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl:
PREFIX rdf:
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema# VTFRYAE8ASYAO1JtUzI=" rel="external" target="_blank">
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
<Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Lorenz Buehmann

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1


@Matthew Correct me if something was wrong :D


Cheers,

Lorenz


On 17.10.2017 01:27, Matthew Horridge wrote:
Hi,

You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.

BIND((year(?date) - 1) as ?startYear)

Cheers,

Matthew


On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:

Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

<36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

<2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To: [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
<Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user



_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Matthew Horridge-2
Administrator
Hi Lorenz,

Most of SPARQL syntax is supported, including

- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions

These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)

- Subqueries
- EXITS
- OFFSET
- LIMIT

These things are not supported

- Property Paths

Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.

It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.

Hope this clarifies things.  I should add a help page for the plugin!

Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.

Cheers,

Matthew






On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1


@Matthew Correct me if something was wrong :D


Cheers,

Lorenz


On 17.10.2017 01:27, Matthew Horridge wrote:
Hi,

You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.

BIND((year(?date) - 1) as ?startYear)

Cheers,

Matthew


On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:

Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

<36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

<2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To: [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 

 
Snap shot for Event Occurred
 



Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
<Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user



_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Dipali
Thanks Lorenz and Matthew for your response.

But still I am facing the issue with filtering.

Issue is in my   data I don't have timezone added..so when I use the BIND function with YEAR/MONTH  it return me the  empty data.

Please see the below screen shot.

In the first BIND function I used the date from the result of  "nowTime" and I can see the result as 10 in the month.
But from the third BIND function, I  get the empty result as  "?date" don't have the timezone in it.


As the YEAR/MONTH function return the empty data, I cannot filter my data between two dates .

Is it compulsory to have timezone specified in the datetime format to use the functions YEAR /MONTH or DAY?

Thanks,
Dipali

From: Matthew Horridge <[hidden email]>
Sent: Tue, 17 Oct 2017 23:43:47
To: User support for WebProtege and Protege Desktop <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Lorenz,
 
Most of SPARQL syntax is supported, including
 
- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions
 
These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)
 
- Subqueries
- EXITS
- OFFSET
- LIMIT
 
These things are not supported
 
- Property Paths
 
Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.
 
It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.
 
Hope this clarifies things.  I should add a help page for the plugin!
 
Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.
 
Cheers,
 
Matthew
 
 
 
 
 
 
On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:
 

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1

 

@Matthew Correct me if something was wrong :D

 

Cheers,

Lorenz

 
On 17.10.2017 01:27, Matthew Horridge wrote:
[hidden email]" class=""> Hi,
 
You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.
 
BIND((year(?date) - 1) as ?startYear)
 
Cheers,
 
Matthew
 
 
On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:
 
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

[hidden email]" class=""><36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

[hidden email]" class=""><2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To: [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl:
PREFIX rdf:
PREFIX rdfs:
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 
[hidden email]" type="application/x-apple-msg-attachment" height="462" width="795">
 
Snap shot for Event Occurred
 
[hidden email]" type="application/x-apple-msg-attachment" height="265" width="645">


Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
[hidden email]" class=""><Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Matthew Horridge-2
Administrator
Hi Dipali,

Please can you update the sparql plugin to the latest version (5.0.0) and see if the problem persists?

Cheers,

Matthew


On 22 Oct 2017, at 23:48, Dipali <[hidden email]> wrote:

Thanks Lorenz and Matthew for your response.

But still I am facing the issue with filtering.

Issue is in my   data I don't have timezone added..so when I use the BIND function with YEAR/MONTH  it return me the  empty data.

Please see the below screen shot.

In the first BIND function I used the date from the result of  "nowTime" and I can see the result as 10 in the month.
But from the third BIND function, I  get the empty result as  "?date" don't have the timezone in it.
<796ae.1508741331.91.web.rediffmail.com.png>

As the YEAR/MONTH function return the empty data, I cannot filter my data between two dates .

Is it compulsory to have timezone specified in the datetime format to use the functions YEAR /MONTH or DAY?

Thanks,
Dipali

From: Matthew Horridge <[hidden email]>
Sent: Tue, 17 Oct 2017 23:43:47
To: User support for WebProtege and Protege Desktop <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Lorenz,
 
Most of SPARQL syntax is supported, including
 
- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions
 
These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)
 
- Subqueries
- EXITS
- OFFSET
- LIMIT
 
These things are not supported
 
- Property Paths
 
Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.
 
It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.
 
Hope this clarifies things.  I should add a help page for the plugin!
 
Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.
 
Cheers,
 
Matthew
 
 
 
 
 
 
On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:
 

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1

 

@Matthew Correct me if something was wrong :D

 

Cheers,

Lorenz

 
On 17.10.2017 01:27, Matthew Horridge wrote:
[hidden email]" class=""> Hi,
 
You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.
 
BIND((year(?date) - 1) as ?startYear)
 
Cheers,
 
Matthew
 
 
On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:
 
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

[hidden email]" class=""><36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

[hidden email]" class=""><2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To:  [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: 
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: 
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = ) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 
[hidden email]" type="application/x-apple-msg-attachment" height="462" width="795">
 
Snap shot for Event Occurred
 
[hidden email]" type="application/x-apple-msg-attachment" height="265" width="645">


Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
[hidden email]" class=""><Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Dipali
Hi Matthew,
 
I . With the updated snap SPARQL plugin version(5.0.0) also facing  the issue with date format. MONTH,YEAR function require time zone in the date format to get the data.
date_month varibale contain blank value as ?date is not with the format of TIME ZONE added into it.
 
 
 
 
 
II .If I add +1 to get the next month value using  date format required, it returns the blank value. Check the below query.
 
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?date ?measurement ?Value ?measurementTime  ?date_month  ?nowTime ?month  ?nextmonth
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                     eg:belongsTo ?machineid.
        ?machineid  eg:hasCategory ?machinecategory;
                              eg:hasMeasurement  ?measurement;
                         eg:hasNativePin  ?Pin.
                         ?measurement eg:hasValue ?Value;
                                                   eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                
                  BIND(MONTH(?date) AS ?date_month)                               
                  BIND((NOW()) AS ?nowTime)
                  BIND((MONTH("2017-10-23T12:00:31+05:30"^^xsd:dateTime)) AS ?month)
                  BIND(((MONTH("2017-10-23T12:00:31+5:30"^^xsd:dateTime)) + 1 ) AS ?nextmonth) 
                                 
               #    FILTER( ( ?machineid = <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>)  )
                  
                    }  
 ORDER BY ?measurement
 
In the result snapshot shown in the  section I , month contain value 10 but nextmonth value is blank.  

Is there any other setting require to be done to have  this functionality working?

Thanks,
Dipali


From: Matthew Horridge <[hidden email]>
Sent: Wed, 01 Nov 2017 04:53:30
To: User support for WebProtege and Protege Desktop <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Dipali,
 
Please can you update the sparql plugin to the latest version (5.0.0) and see if the problem persists?
 
Cheers,
 
Matthew
 
 
On 22 Oct 2017, at 23:48, Dipali <[hidden email]> wrote:
 
Thanks Lorenz and Matthew for your response.

But still I am facing the issue with filtering.

Issue is in my   data I don't have timezone added..so when I use the BIND function with YEAR/MONTH  it return me the  empty data.

Please see the below screen shot.

In the first BIND function I used the date from the result of  "nowTime" and I can see the result as 10 in the month.
But from the third BIND function, I  get the empty result as  "?date" don't have the timezone in it.
[hidden email]"><796ae.1508741331.91.web.rediffmail.com.png>

As the YEAR/MONTH function return the empty data, I cannot filter my data between two dates .

Is it compulsory to have timezone specified in the datetime format to use the functions YEAR /MONTH or DAY?

Thanks,
Dipali

From: Matthew Horridge <[hidden email]>
Sent: Tue, 17 Oct 2017 23:43:47
To: User support for WebProtege and Protege Desktop <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Lorenz,
 
Most of SPARQL syntax is supported, including
 
- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions
 
These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)
 
- Subqueries
- EXITS
- OFFSET
- LIMIT
 
These things are not supported
 
- Property Paths
 
Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.
 
It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.
 
Hope this clarifies things.  I should add a help page for the plugin!
 
Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.
 
Cheers,
 
Matthew
 
 
 
 
 
 
On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:
 

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1

 

@Matthew Correct me if something was wrong :D

 

Cheers,

Lorenz

 
On 17.10.2017 01:27, Matthew Horridge wrote:
[hidden email]" class=""> Hi,
 
You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.
 
BIND((year(?date) - 1) as ?startYear)
 
Cheers,
 
Matthew
 
 
On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:
 
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

[hidden email]" class=""><36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

[hidden email]" class=""><2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To:  [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: 
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema#/VDU== UHdQa1JtVjc=" moz-do-not-send="true" rel="external" target="_blank">
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = <a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034/B2MFNAY7AiUFPlZpXz4== UXYGPVtkXj8=" moz-do-not-send="true" rel="external" target="_blank">) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 
[hidden email]" type="application/x-apple-msg-attachment" height="462" width="795">
 
Snap shot for Event Occurred
 
[hidden email]" type="application/x-apple-msg-attachment" height="265" width="645">


Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
<a class="moz-txt-link-freetext" href="https://mailman.stanford.edu/mailman/listinfo/protege-user%20VHNTaFRrVjc== UDE=" moz-do-not-send="true" rel="external" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
[hidden email]" class=""><Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Matthew Horridge-2
Administrator
Hi Dipali,

The MONTH, YEAR functions require a valid xsd:dateTime as the value.  The lexical values for xsd:dateTime must have a timezone specified (like you have below with +05:30 …. or with Z).  The values bound to ?date are not valid lexical values for xsd:dateTime.  This will cause the MONTH function to return an error and hence you’re seeing empty results for ?date_month.

You should correct the values of the e.g:date property so that they are valid values, if you can.  You could work around this in SPARQL by doing something like this:

BIND(MONTH(xsd:dateTime(CONCAT(STR(?date), "+00:00"))) AS ?month)

which basically takes the value bound to ?date, converts it to a string, concatenates that string with a time zone and then casts the resulting string to an xsd:dateTime.  Fixing the actual data would be preferable though.

The ?nextmonth value is blank because "2017-10-23T12:00:31+5:30” is not a valid lexical value for xsd:dateTime.  It should be "2017-10-23T12:00:31+05:30” – note the difference in the timezone i.e. +5:30, which is incorrect, versus +05:30, which is correct.

Cheers,

Matthew


On 5 Nov 2017, at 22:33, Dipali <[hidden email]> wrote:

Hi Matthew,
 
I . With the updated snap SPARQL plugin version(5.0.0) also facing  the issue with date format. MONTH,YEAR function require time zone in the date format to get the data.
date_month varibale contain blank value as ?date is not with the format of TIME ZONE added into it.
 
 <48270.1509950036.37.web.rediffmail.com.png>
 
 
 
II .If I add +1 to get the next month value using  date format required, it returns the blank value. Check the below query.
 
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?date ?measurement ?Value ?measurementTime  ?date_month  ?nowTime ?month  ?nextmonth
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                     eg:belongsTo ?machineid.
        ?machineid  eg:hasCategory ?machinecategory;
                              eg:hasMeasurement  ?measurement;
                         eg:hasNativePin  ?Pin.
                         ?measurement eg:hasValue ?Value;
                                                   eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                
                  BIND(MONTH(?date) AS ?date_month)                               
                  BIND((NOW()) AS ?nowTime)
                  BIND((MONTH("2017-10-23T12:00:31+05:30"^^xsd:dateTime)) AS ?month)
                  BIND(((MONTH("2017-10-23T12:00:31+5:30"^^xsd:dateTime)) + 1 ) AS ?nextmonth) 
                                 
               #    FILTER( ( ?machineid = <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>)  )
                  
                    }  
 ORDER BY ?measurement
 
In the result snapshot shown in the  section I , month contain value 10 but nextmonth value is blank.  

Is there any other setting require to be done to have  this functionality working?

Thanks,
Dipali


From: Matthew Horridge <[hidden email]>
Sent: Wed, 01 Nov 2017 04:53:30
To: User support for WebProtege and Protege Desktop <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Dipali,
 
Please can you update the sparql plugin to the latest version (5.0.0) and see if the problem persists?
 
Cheers,
 
Matthew
 
 
On 22 Oct 2017, at 23:48, Dipali <[hidden email]> wrote:
 
Thanks Lorenz and Matthew for your response.

But still I am facing the issue with filtering.

Issue is in my   data I don't have timezone added..so when I use the BIND function with YEAR/MONTH  it return me the  empty data.

Please see the below screen shot.

In the first BIND function I used the date from the result of  "nowTime" and I can see the result as 10 in the month.
But from the third BIND function, I  get the empty result as  "?date" don't have the timezone in it.
[hidden email]"><796ae.1508741331.91.web.rediffmail.com.png>

As the YEAR/MONTH function return the empty data, I cannot filter my data between two dates .

Is it compulsory to have timezone specified in the datetime format to use the functions YEAR /MONTH or DAY?

Thanks,
Dipali

From: Matthew Horridge <[hidden email]>
Sent: Tue, 17 Oct 2017 23:43:47
To: User support for WebProtege and Protege Desktop <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Lorenz,
 
Most of SPARQL syntax is supported, including
 
- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions
 
These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)
 
- Subqueries
- EXITS
- OFFSET
- LIMIT
 
These things are not supported
 
- Property Paths
 
Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.
 
It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.
 
Hope this clarifies things.  I should add a help page for the plugin!
 
Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.
 
Cheers,
 
Matthew
 
 
 
 
 
 
On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:
 

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1

 

@Matthew Correct me if something was wrong :D

 

Cheers,

Lorenz

 
On 17.10.2017 01:27, Matthew Horridge wrote:
[hidden email]" class=""> Hi,
 
You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.
 
BIND((year(?date) - 1) as ?startYear)
 
Cheers,
 
Matthew
 
 
On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:
 
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

[hidden email]" class=""><36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

[hidden email]" class=""><2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To:  [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: 
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: 
PREFIX rdf: 
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema#/VDU== UHdQa1JtVjc=" moz-do-not-send="true" rel="external" target="_blank">
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory .
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = <a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034/B2MFNAY7AiUFPlZpXz4== UXYGPVtkXj8=" moz-do-not-send="true" rel="external" target="_blank">) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 
[hidden email]" type="application/x-apple-msg-attachment" height="462" width="795">
 
Snap shot for Event Occurred
 
[hidden email]" type="application/x-apple-msg-attachment" height="265" width="645">


Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
[hidden email]" class=""><Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user


_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
Reply | Threaded
Open this post in threaded view
|

Re: Filtering between two dates in SPARQL

Dipali
In reply to this post by Dipali
Hello Matthew,

1.  I  cannot change the date format which is there in my data.
So I  tried with the BIND command which is provided below.But  for "BIND(MONTH(xsd:dateTime(CONCAT(STR(?date), "+00:00"))) AS ?date_month)" I cannot see any result.
So I try to debug it with breaking down the command into small steps.

when I apply the xsd:dateTime command to convert the string into dateTime, its getting converted into incorrect format. Date format after  T is hh:mm:ss but I am getting only T hh:mm . seconds part gets deleted which cause again incorrect format of date and doesnot return any result when I use MONTH command.




2.  After correcting the time zone format I am able to get correct value in ?nextmonth.

Thanks,
Dipali


From: Matthew Horridge <[hidden email]>
Sent: Mon, 06 Nov 2017 22:55:41
To: User support for WebProtege and Protege Desktop <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Dipali,
 
The MONTH, YEAR functions require a valid xsd:dateTime as the value.  The lexical values for xsd:dateTime must have a timezone specified (like you have below with +05:30 …. or with Z).  The values bound to ?date are not valid lexical values for xsd:dateTime.  This will cause the MONTH function to return an error and hence you’re seeing empty results for ?date_month.
 
You should correct the values of the e.g:date property so that they are valid values, if you can.  You could work around this in SPARQL by doing something like this:
 
BIND(MONTH(xsd:dateTime(CONCAT(STR(?date), "+00:00"))) AS ?month)
 
which basically takes the value bound to ?date, converts it to a string, concatenates that string with a time zone and then casts the resulting string to an xsd:dateTime.  Fixing the actual data would be preferable though.
 
The ?nextmonth value is blank because "2017-10-23T12:00:31+5:30” is not a valid lexical value for xsd:dateTime.  It should be "2017-10-23T12:00:31+05:30” – note the difference in the timezone i.e. +5:30, which is incorrect, versus +05:30, which is correct.
 
Cheers,
 
Matthew
 
 
On 5 Nov 2017, at 22:33, Dipali <[hidden email]> wrote:
 
Hi Matthew,
 
I . With the updated snap SPARQL plugin version(5.0.0) also facing  the issue with date format. MONTH,YEAR function require time zone in the date format to get the data.
date_month varibale contain blank value as ?date is not with the format of TIME ZONE added into it.
 
 [hidden email]"><48270.1509950036.37.web.rediffmail.com.png>
 
 
 
II .If I add +1 to get the next month value using  date format required, it returns the blank value. Check the below query.
 
PREFIX owl: <http://www.w3.org/2002/07/owl#>
PREFIX rdf: <<a href="http://www.w3.org/1999/02/22-rdf-syntax-ns#/VzNWZwQ5ACcHPFBvAmM= UjM=" rel="external" target="_blank">http://www.w3.org/1999/02/22-rdf-syntax-ns#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX eg:<<a href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86# VnEEP1doVjc==" rel="external" target="_blank">http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#>
 
SELECT  ?eventid  ?date ?measurement ?Value ?measurementTime  ?date_month  ?nowTime ?month  ?nextmonth
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                     eg:belongsTo ?machineid.
        ?machineid  eg:hasCategory ?machinecategory;
                              eg:hasMeasurement  ?measurement;
                         eg:hasNativePin  ?Pin.
                         ?measurement eg:hasValue ?Value;
                                                   eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature>.
                
                  BIND(MONTH(?date) AS ?date_month)                               
                  BIND((NOW()) AS ?nowTime)
                  BIND((MONTH("2017-10-23T12:00:31+05:30"^^xsd:dateTime)) AS ?month)
                  BIND(((MONTH("2017-10-23T12:00:31+5:30"^^xsd:dateTime)) + 1 ) AS ?nextmonth) 
                                 
               #    FILTER( ( ?machineid = <<a href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034= XjpWZ1BtAyQBOgc4UzI=" rel="external" target="_blank">http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034>)  )
                  
                    }  
 ORDER BY ?measurement
 
In the result snapshot shown in the  section I , month contain value 10 but nextmonth value is blank.  

Is there any other setting require to be done to have  this functionality working?

Thanks,
Dipali


From: Matthew Horridge <[hidden email]>
Sent: Wed, 01 Nov 2017 04:53:30
To: User support for WebProtege and Protege Desktop <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Dipali,
 
Please can you update the sparql plugin to the latest version (5.0.0) and see if the problem persists?
 
Cheers,
 
Matthew
 
 
On 22 Oct 2017, at 23:48, Dipali <[hidden email]> wrote:
 
Thanks Lorenz and Matthew for your response.

But still I am facing the issue with filtering.

Issue is in my   data I don't have timezone added..so when I use the BIND function with YEAR/MONTH  it return me the  empty data.

Please see the below screen shot.

In the first BIND function I used the date from the result of  "nowTime" and I can see the result as 10 in the month.
But from the third BIND function, I  get the empty result as  "?date" don't have the timezone in it.
[hidden email]"><796ae.1508741331.91.web.rediffmail.com.png>

As the YEAR/MONTH function return the empty data, I cannot filter my data between two dates .

Is it compulsory to have timezone specified in the datetime format to use the functions YEAR /MONTH or DAY?

Thanks,
Dipali

From: Matthew Horridge <[hidden email]>
Sent: Tue, 17 Oct 2017 23:43:47
To: User support for WebProtege and Protege Desktop <[hidden email]>
Cc: "[hidden email]" <[hidden email]>
Subject: Re: [protege-user] Filtering between two dates in SPARQL

Hi Lorenz,
 
Most of SPARQL syntax is supported, including
 
- SELECT
- CONSTRUCT
- OPTIONAL
- MINUS
- UNION
- BIND
- FILTER
- Most built ins.  (A handful have not been implemented, e.g. SHA256 etc. but could be if there is demand)
- Logical not, and, or 
- ORDER BY
- GROUP BY
- Project Expressions
 
These things are not supported but could be if there is a specific demand for them by Snap SPARQL users (I just haven’t got around to implementing them)
 
- Subqueries
- EXITS
- OFFSET
- LIMIT
 
These things are not supported
 
- Property Paths
 
Property Paths could be supported to some extent, but Snap is based on the OWL Entailment regime so see the discussion here (<a href="https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths=/AGE= BGU==" rel="external" target="_blank">https://www.w3.org/TR/2013/REC-sparql11-entailment-20130321/#PropertyPaths).  I kind of think some abuse of the syntax would be nice, to query the class hierarchy so ?x rdfs:subClassOf ?y could be direct subclass of and ?x rdfs:subClassOf+ ?y could be used for indirect subclass of…. this is at odds with the OWL entailment regime though.
 
It’s also important to consider that the OWL 2 direct semantics entailment regime imposes some requirements on queries and results.  The most important is that a basic graph pattern in a query must correspond to an extended (with variables) OWL 2 DL ontology.  All variables must be typed and each variable can only have one type.  Snap-SPARQL relaxes the variable typing requirement so that if the types can be inferred unambiguously then they don’t need to be included explicitly e.g. given {?x rdfs:subClassOf ?y} then ?x is an owl:Class as is ?y.  On the other hand, {?s ?p ?o} is not legal because it’s not possible to determine the types of the variables… ?s could be a class, property or individual, ?p could be an object property, data property or annotation property, etc. etc.
 
Hope this clarifies things.  I should add a help page for the plugin!
 
Above all, Snap-SPARQL is only a plugin to experiment with the OWL entailment regime.  It’s not a robust SPARQL solution and should not be used in place of such a solution.
 
Cheers,
 
Matthew
 
 
 
 
 
 
On 17 Oct 2017, at 01:20, Lorenz Buehmann <[hidden email]> wrote:
 

Ah, sure. I just added the VALUES for "easier" understanding, but sure, it's totally superfluous here. Thus, please remove it, and replace ?d in the BIND clauses by 1.

Just as a comment, my solution is based on SPARQL 1.1 - I don't know whether features like BIND etc. are supported by Snap SPARQL. Do you really need inferencing in your query? Otherwise, you should use the default SPARQL view of Protege. This is based on RDF4J and should cover full SPARQL 1.1

 

@Matthew Correct me if something was wrong :D

 

Cheers,

Lorenz

 
On 17.10.2017 01:27, Matthew Horridge wrote:
[hidden email]" class=""> Hi,
 
You’re using Snap-SPARQL, which doesn’t (currently) support VALUES.  I’m wondering if you could just replace the ?d with explicit use of the number 1, given that this is the only value.  i.e.
 
BIND((year(?date) - 1) as ?startYear)
 
Cheers,
 
Matthew
 
 
On 16 Oct 2017, at 05:20, Dipali <[hidden email]> wrote:
 
Thanks for your reply.

When I try to write the query the way its suggested by you, I am getting  syntax error at VALUES declaration.

[hidden email]" class=""><36d4f.1508156445.32.web.rediffmail.com.png>

I am using Protege 5.2  version.Installed Plugin versions are:

[hidden email]" class=""><2944e.1508156445.35.web.rediffmail.com.png>
In my previous email, I have attached sample data.
Attaching it again for your reference(ontology_tutorial.zip)


Thanks,
Dipali

From: Lorenz Buehmann <[hidden email]>
Sent: Fri, 13 Oct 2017 21:00:50
To:  [hidden email]
Subject: Re: [protege-user] Filtering between two dates in SPARQL

* Your event occurred on 20th Feb

* your filter FILTER (  ?measurementTime <=?date ) asks for everything before or at this date, thus it's clear that you get results with 18th Feb and 19th Feb

I don't know if I understand correctly, but if you want to have everything around this date, +- 1 day, then you have can use BIND and create those dates.

Unfortunately, you haven't shared sample data, thus, I can't test it on your data. Sketch:

 

PREFIX owl: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2002/07/owl#=== XztRYFNuBCMBOlRrB2Y==" moz-do-not-send="true" rel="external" target="_blank">
PREFIX rdf: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/1999/02/22-rdf-syntax-ns#=/VDU=== UzI=" moz-do-not-send="true" rel="external" target="_blank">
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema#%20VTFRYAE8ASYAO1JtUzI==/XztcbVNuACdWbVBvBGU= XjoHNlJvVHMKMVtkA2I= ByALMFNsBWQ=" moz-do-not-send="true" rel="external" target="_blank">
PREFIX eg:<a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#=/VTQ=/UTA== UzddbFJvBiFXbAc4UzI=" moz-do-not-send="true" rel="external" target="_blank">
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature/Vjc==/VjIAMVJvUHcGPVZpUTA= VDBWZ1xhBCMLMFVqAGE==" moz-do-not-send="true" rel="external" target="_blank">.
                 FILTER (?machineid = )

# year as duration here

VALUES ?d { 1 }

# compute start and end year S and E

BIND((year(?date) - ?d) as ?startYear)
BIND((year(?date) + ?d) as ?endYear)

# apply filter S >= measurementTime <= E

FILTER(year(?measurementTime) >= ?startYear && year(?measurementTime) <= ?endYear)

}  ORDER BY ?measurement

 
On 13.10.2017 15:41, Dipali wrote:
[hidden email]">Hello,
 
In my ontology , I have some event and  its date when it occurred.
Similarly I have measurement data  in a different  table.
For an event ,  which is happened on particular date( this is like a variable in my query),
I want to  use this date as reference and filter the measurement data between one day before and one day after from event data.
 
But I am not able to get the correct syntax to write the filtering.
 
I am able to filter the data less that the date on which event happened or data after event happened.( ?measurementTime <=?date   --  Here date has data when event occurred and  measurement Time is date for the measurement taken  )
 
PREFIX owl: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2002/07/owl#==== BiFRagY5UjM=" moz-do-not-send="true" rel="external" target="_blank">
PREFIX rdf: 
PREFIX rdfs: <a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2000/01/rdf-schema#/VDU== UHdQa1JtVjc=/B2Y==" moz-do-not-send="true" rel="external" target="_blank">
PREFIX eg:
 
SELECT  ?eventid  ?machineid  ?dtccode  ?date ?measurement ?Value ?measurementTime
WHERE{ ?eventid  eg:hasFaultCode  ?dtccode;
                               eg:date ?date;
                             eg:belongsTo ?machineid.
                ?machineid  eg:hasCategory ?machinecategory;
                                      eg:hasMeasurement  ?measurement;
                                 eg:hasNativePin  ?Pin.
                                 ?measurement eg:hasValue ?Value;
                                                           eg:hasMeasurementTime ?measurementTime.
                 ?dtccode eg:hasFaultCategory <a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#HydraulicOilTemperature==== XjpRYAE8AiVUbwE A2I=" moz-do-not-send="true" rel="external" target="_blank">.
                 FILTER (  ( ?measurementTime <=?date )&&   ?machineid = <a class="moz-txt-link-rfc2396E" href="http://www.semanticweb.org/dt73315/ontologies/2017/1/untitled-ontology-86#mc/760034/B2MFNAY7AiUFPlZpXz4== UXYGPVtkXj8===" moz-do-not-send="true" rel="external" target="_blank">) }  
 ORDER BY ?measurement
 
 
With the query what I have written, I am getting the data  from 18th to 20th Feb.
But not getting the syntax to filter the data  which will give me the result  as filter data between 19th Feb to 21st Feb as event occurred on 20th Feb.(As highlighted below in Brown Box)
As date will change every time , I cannot use hard coded date values in the filter.
 
[hidden email]" type="application/x-apple-msg-attachment" height="462" width="795">
 
Snap shot for Event Occurred
 
[hidden email]" type="application/x-apple-msg-attachment" height="265" width="645">


Thanks,
Dipali
 
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
<a href="https://mailman.stanford.edu/mailman/listinfo/protege-user==== U3RXbFZpA2I=" moz-do-not-send="true" rel="external" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user
[hidden email]" class=""><Ontology_Tutorial.zip>_______________________________________________
protege-user mailing list
[hidden email]
<a class="moz-txt-link-freetext" href="https://mailman.stanford.edu/mailman/listinfo/protege-user/VzNXZgA9AyRWbVVqB2Y== UDRVZFxhACcHPFJtB2Y= UTVVZFxhAyQHPAI9Xj8=" rel="external" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user
 
 
 
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
<a href="https://mailman.stanford.edu/mailman/listinfo/protege-user/XztTYldqCywAOwY5VDU== VzY==" rel="external" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
<a href="https://mailman.stanford.edu/mailman/listinfo/protege-user=/AmZXZgc6UHcCOVNsVDU= Vjc= U3RWbVJtB2Y=" rel="external" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
<a href="https://mailman.stanford.edu/mailman/listinfo/protege-user B2Y==" rel="external" style="font-family: Helvetica; font-size: 12px; font-style: normal; font-variant-caps: normal; font-weight: normal; letter-spacing: normal; orphans: auto; text-align: start; text-indent: 0px; text-transform: none; white-space: normal; widows: auto; word-spacing: 0px; -webkit-text-size-adjust: auto; -webkit-text-stroke-width: 0px;" target="_blank">https://mailman.stanford.edu/mailman/listinfo/protege-user
_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user

_______________________________________________
protege-user mailing list
[hidden email]
https://mailman.stanford.edu/mailman/listinfo/protege-user