2

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?

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
Aleksandar Stojadinovic
  • 4,851
  • 1
  • 34
  • 56

1 Answers1

-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.

Matt Johnson-Pint
  • 230,703
  • 74
  • 448
  • 575
  • 1
    I don't want to normalize the time to UTC because I want to preserve the time zone offset. It is used to resolve has the event happened in daytime or night, for example. As foolish as it may seem, but the time zone is valid data as everything else, I don't want to destroy it. Especially because 4Store should enable queries with the time zone included (absolute comparison was something before or after no matter of the offset). Sorry about the expression, locality differences :). I'm not the one that downvoted :). – Aleksandar Stojadinovic Sep 28 '13 at 09:34
  • Understood. It will come down to whether or not 4Store can do the conversion to UTC for you. Like I said, other databases *will* do this, but it has to be supported natively. If 4Store just stores and indexes as a raw string, then you are out of luck. One idea would be to store the field *twice* - once with its original offset, and once with the offset normalized to UTC. – Matt Johnson-Pint Sep 28 '13 at 17:16
  • It does compare dates, so it is not a regular string. The problem is the comparison deviates from the specification. – Aleksandar Stojadinovic Sep 28 '13 at 17:59
  • I've been digging for hours through various documentation on 4Store, RDF and SPARQL, and I cannot find anywhere that describes how an `xsd:dateTime` is manipulated during storage or indexing. I think it is storing whatever you give it. If you can point me somewhere that says otherwise, please do. – Matt Johnson-Pint Sep 28 '13 at 18:02
  • I've also reread your question multiple times and the wording is confusing. In one part of the question you say that it is shifting the values for comparison, in another part you say it is ignored. Which is it? Wouldn't you *want* it to shift for comparison? Otherwise you're not talking about the same moments in time. Perhaps I am just misunderstanding what you're asking. Please edit your question to include what you are originally storing into the database, how 4Store is storing it, how you see it shifted, what you are expecting, and anything else that will make it clearer. Thanks. – Matt Johnson-Pint Sep 28 '13 at 18:09