Functions on Date and Time Values

This topic describes the built-in SPARQL functions that operate on date and time values.

• DAY: Returns the day part of a date or dateTime value.
• DUR_TO_USECS: Converts a duration value to microseconds.
• HOURS: Returns the hours part of a time or dateTime value.
• MINUTES: Returns the minutes part of a time or dateTime value.
• MONTH: Returns the month part of a date or dateTime value.
• NOW: Returns a timestamp of the current date and time on the instance.
• SECONDS_DBL: Returns the seconds part of a time or dateTime value.
• TIMEZONE: Returns the timezone part of a dateTime value as an xsd:dayTimeDuration value.
• TZ: Returns the timezone part of a dateTime value as a simple literal value.
• USECS_TO_DUR: Converts a microseconds value to a duration.
• YEAR: Returns the year part of a date or dateTime value.

Date and Time Function Syntax

Use the following syntax when incorporating date and time functions in queries:

FUNCTION(expression)

Note: The NOW function does not take any input. Use the following syntax for NOW:

NOW()

Date and Time Function Examples

The following example queries the sample Tickit data set to return the month and day of the birthday for 100 people.

SELECT ?fname ?lname (MONTH(?birthday) AS ?month) (DAY(?birthday) AS ?day)
FROM <tickit>
WHERE {
  ?person <birthday> ?birthday .
  ?person <firstname> ?fname .
  ?person <lastname> ?lname .
}
ORDER BY ?month
LIMIT 100

fname      | lname      | month | day
-----------+------------+-------+-----
Christian  | Shannon    |     1 |   1
Amaya      | Terrell    |     1 |   6
Elton      | Britt      |     1 |   5
Yoshi      | Donaldson  |     1 |   5
Adam       | Durham     |     1 |  26
Fleur      | Wilkins    |     1 |  11
Wallace    | Mooney     |     1 |  23
Wanda      | Delgado    |     1 |   1
Karina     | Ruiz       |     1 |  22
...
100 rows

The query below uses the MONTH function to determine the most popular month to hold events, based on the number of events that occur in each month.

SELECT ?month (COUNT(?eventid) AS ?num_events)
FROM <tickit>
WHERE {
  { SELECT ?eventid (MONTH(?eventtime) AS ?month)
    WHERE {
      ?eventid <starttime> ?eventtime .
      ?sale <eventid> ?eventid .
    }
  }
}
GROUP BY ?month
ORDER BY DESC(?num_events)

month | num_events
------+------------
    3 |      34935
    9 |      34346
   10 |      33856
    7 |      33770
    5 |      33638
   11 |      33599
    8 |      33542
   12 |      33022
    4 |      32864
    6 |      32418
    2 |      22503
    1 |       6460
12 rows

This example query uses the NOW and YEAR functions to calculate the approximate ages of 100 people in the sample Tickit data set. The resulting age values are approximations because the calculation excludes days and months.

SELECT ?person ((YEAR(?date))-(YEAR(xsd:dateTime(?birthdate))) AS ?age)
FROM <tickit> 
WHERE {
  ?person <birthday> ?birthdate .
  BIND(xsd:dateTime(NOW()) AS ?date)
}
ORDER BY ?person
LIMIT 100

person      | age
------------+-----
person1     |  79
person10    |  81
person100   |  51
person1000  |  75
person10000 |  24
person10001 |  36
person10002 |  53
person10003 |  28
person10004 |  75
...
100 rows