4Store time zone filter not accurate

Question

I'm having issues filtering records between two time filters that include time zones in 4Store. My records are currently mostly in the +02:00 time zone, and it is a xsd:dateTime type.

When I try a filter like this:

FILTER (?time >= xsd:dateTime('2013-08-02T01:00:00.000+02:00') && ?time <=
xsd:dateTime('2013-08-03T22:00:00.000+02:00'))

or

FILTER (?time >= "2013-08-02T01:00:00.000+02:00"^^xsd:dateTime &&
?time <= "2013-08-03T22:00:00.000+02:00"^^xsd:dateTime)

The database shifts those times for the time zone amount, and then compares them literally to the times in the database, ignoring their time zone. Which means, when I want the time from the range in the examples, I have to remove the time zone, or put Z or +00:00. When I read the times, they are correctly written, with their time zone which is +02:00. It somehow ignores the time zone comparison, but when I put the zone in the query the store shifts the time. This is going to be a major confusion when I have more time zones in the system.

Can anybody give some advice regarding this?

Answer 1

<rant> That's a "time zone offset", not a "time zone". Please use the correct terminology to avoid confusion. (But I understood your question just fine.) </rant>

The best advice would be to apply the offset before you store your data, such that the value stored in the database is at UTC. For example, if you have 2013-08-03T22:00:00.000+02:00 you would store 2013-08-03T20:00:00.000Z. Since the offset was two hours ahead of UTC, you subtract two hours to get back to the UTC time. Most languages have a way to do this without an actual subtraction operation, so use that when available.

When you query, you do the same thing. Normalize your query inputs to UTC before passing them into the filter. Then everything lines up as desired.

I am not familiar with 4store, but many databases will do this kind of conversion for you automatically. Some will even let you store the value with the original offset and only do the conversion when building indexes. If 4store has facilities for this, then you should use them. I checked the documentation and didn't find anything one way or the other about how it deals with dates.