Hi,

Is it possible to get the day of the week from a dateTime literal using SPARQL? I'm trying to infer whether a certain dateTime literal (retrieved from the 'now()' function) is on a certain day of the week. I'm aware of the year, month, day, hours, minutes, seconds and timezone functions, but not of a similar function returning the day of the week. If there's no such function, is there another way to infer the day of the week?

Regards, Jacco

asked 24 Mar '12, 16:04

Jacco's gravatar image

Jacco
4315
accept rate: 0%

edited 24 Mar '12, 22:55

Signified's gravatar image

Signified ♦
23.9k1623


Okay, SPARQL 1.1... (Note SPARQL 1.1 has floor. You can calculate a % b (modulo) using a - (floor(a/b) * b).)

Using the Gaussian function in @harschware's link, this should give an ID for the current day of the week:

(EDIT: formula now (i) adjusts for January, February, (ii) ensures positive result, (iii) doesn't assume a four digit year.)

PREFIX  xsd:  <http://www.w3.org/2001/XMLSchema#>
SELECT ?date ?dayID 
WHERE { 
    BIND (now() AS ?date)
    BIND (day(?date) AS ?day)
    BIND (month(?date) AS ?month)
    BIND (year(?date) AS ?year)

    ############################################
    ## Gaussian algorithm for day of the week ##

    # Subtract 1 to year if January or February
    BIND (IF(?month<=2, 1, 0) AS ?jf)
    BIND (?year - ?jf AS ?adjyear)

    #century and year
    BIND (floor(?adjyear/100) as ?c) #?c is the century
    BIND (?adjyear - (?c * 100) as ?y) #?y is the year

    # x2 = (month + 9) % 12 + 1
    BIND (?month+9 AS ?x1)
    BIND (xsd:integer(?x1 - floor(?x1/12)*12 + 1) AS ?x2)

    # body of formula
    BIND ((?day + floor((2.6 * ?x2) - 0.2) + ?y + floor(?y/4) + floor(?c/4) - (2 * ?c)) AS ?i)
    BIND (?i - (floor(?i/7) * 7) AS ?dayIDx) # ?i % 7

    # ensure result is positive
    BIND (xsd:integer(IF(?dayIDx < 0, ?dayIDx + 7 , ?dayIDx)) AS ?dayID)

    ############################################
}

?dayID gives you 0–6 where 0 = Sunday, 1 = Monday, etc.

You can test this here, with todays "day" here.


If you wanted to return names of the days, one quick trick would be to add triples of the following form to the store (assuming you have write access):

:Sunday :dayID 0 ; rdfs:label "Sunday"@en .
:Monday :dayID 1 ; rdfs:label "Monday"@en .
...

and then add:

?day :dayID ?dayID ; rdfs:label ?dayName .

to your query to retrieve the day name.


If you don't have write access, you could encode the day names into the query:

PREFIX  xsd:  <http://www.w3.org/2001/XMLSchema#>
SELECT ?date ?dayName
WHERE { 
    #calculate ?dayID as above

    BIND (
      IF(?dayID = 0, "Sunday", 
       IF(?dayID = 1, "Monday",
        IF(?dayID = 2, "Tuesday",
         IF(?dayID = 3, "Wednesday",
          IF(?dayID = 4, "Thursday",
           IF(?dayID = 5, "Friday",
            IF(?dayID = 6, "Saturday", "Unknown")
      )))))) AS ?dayName
    )
}

Which gives the answer here. (Thanks to @database_animal for pointing me to the if–then–else ladder pattern.)

permanent link

answered 24 Mar '12, 22:09

Signified's gravatar image

Signified ♦
23.9k1623
accept rate: 37%

edited 25 Mar '12, 13:12

Nice, thanks for this! Really helpful. I didn't know there was a floor function in SPARQL 1.1. But your query doesn't substract the year with 1 if the month is january or february

(25 Mar '12, 07:30) Jacco Jacco's gravatar image

@Jacco, good point: I missed that, but it's now fixed in the formula. With conditional branching it would be easier (if jan|feb then ...) but instead I implemented it as ?jf = ceil(?month/2) % 6 % 5 % 4 % 3 % 2, which gives a 1 if Jan|Feb and 0 otherwise. (There might be a more elegant way.)

I also added something to make sure the result is positive, and now calculate the century/year using .%100 and floor(./100) instead of parsing it with substrings. (This should be more robust for years that don't have four digits.)

(25 Mar '12, 10:15) Signified ♦ Signified's gravatar image

UDPATE: Used conditions to clean up the formula a bit.

(25 Mar '12, 13:13) Signified ♦ Signified's gravatar image
2

Wow, awesome! Realy clever query :) Thanks again

(25 Mar '12, 13:24) Jacco Jacco's gravatar image

Not a sure if there is a function that will do it for you that is available in SPARQL. I'll let others more familiar with the spec help you with that, but if there isn't I think it would be a great exercise to see if you could roll your own using basic math functions (if enough of those are available). The formula for doing that can be found here

permanent link

answered 24 Mar '12, 17:58

harschware's gravatar image

harschware ♦
7.7k1616
accept rate: 20%

Thanks, I'll look into that formula.

Edit: Nice formula, will be useful to remember! Sadly SPARQL doesn't support modulus or floor calculations.

(24 Mar '12, 19:20) Jacco Jacco's gravatar image

You were right about being a great exercise. My SPARQL foo has increased by three bar, particularly regarding some of the new functions and casting (which I think is poorly explained in the doc if @lee sees this; would be great to have an example since it's not clear that the cast function name is the datatype URI; I thought it was int, dbl etc.).

(24 Mar '12, 22:53) Signified ♦ Signified's gravatar image

I think I mighta gotten stuck with the modulus operator being missing so thanks for proving it could be done. And, since we're hoping @lee is listening... modulus operator, case function and of course, dayOfWeek function would also be great. :-)

(26 Mar '12, 00:33) harschware ♦ harschware's gravatar image
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×1,289
×162
×17

question asked: 24 Mar '12, 16:04

question was seen: 3,009 times

last updated: 26 Mar '12, 00:33