Date and Time Functions

This topic describes the date, time, and duration functions in Graph Lakehouse.

DATE: Returns an xsd:date value based on the specified year, month, and day.
DATETIME (or xsd:dateTime): Returns a dateTime value from the given long, double, date, or time value.
DAY: Returns the day of the month from the specified date or dateTime.
DAYSFROMDURATION: Returns the days portion of a duration value.
DUR_TO_MILLIS: Calculates the time in milliseconds from a duration value.
DUR_TO_USECS: Converts a duration value to microseconds.
FORMATDATETIME: Converts a value to a string in the specified dateTime format.
FORMATDURATION: Converts a value into a string in the specified duration format
HOURS: Returns the hour portion of the given time or dateTime value.
MASKEDDATETIME: Replaces the year, month, day, hour, minute, second, and millisecond values for the given date or dateTime value with the new date and time values that you specify.
MILLIS_TO_DUR: Converts milliseconds to a duration value.
MINUTES: Returns the minutes portion of the given time or dateTime value.
MONTH: Returns the month portion of the given date or dateTime value.
NOW: Returns the current server date and time.
NOWMILLIS: Returns the current server date and time in epoch milliseconds.
PARSEDATE: Attempts to convert the given string to a date, time, or dateTime value.
SECONDS_DBL: Returns the seconds portion of the given dateTime value.
TIME: Returns an xsd:time value based on the specified hour, minute, and second values.
TIMEZONE: Returns as a duration the timezone from a dateTime value.
TODAY: Returns today's date based on the server date.
TOMILLIS: Converts a date or dateTime value to milliseconds.
TZ: Returns as a string the timezone from a dateTime value.
USECS_TO_DUR: Converts a microseconds value to a duration.
WEEKDAY: Returns the day of the week from a date or dateTime value.
WEEKNUM: Returns the week of the year in which the given date or dateTime occurs.
YEAR: Returns the year portion of the given dateTime value.
YEARDAY: Returns the day of the year in which the given date or dateTime occurs.

Typographical Conventions

The following list describes the conventions used to document function syntax:

CAPS: Although SPARQL is case-insensitive, SPARQL keywords in this section are written in uppercase for readability.
[ argument ]: Brackets indicate an optional argument or keyword.
|: Means OR. Indicates that you can use one or more of the specified options.

DATE

This function returns an xsd:date value based on the specified year, month, and day values.

Syntax

DATE(year, month, day)

Argument	Type	Description
year	long	A number that represents the year.
month	long	A number that represents the month.
day	long	A number that represents the day.

Returns

Type	Description
date	The date according to the input values.

DATETIME (or xsd:dateTime)

This function returns a dateTime value from the given long, double, date, or time value.

Syntax

DATETIME(value)

Argument	Type	Description
value	long, double, date, time	The value from which to return a dateTime.

Returns

Type	Description
dateTime	The dateTime value.

DAY

This function returns the day of the month from the specified date or dateTime value.

Syntax

DAY(value)

Argument	Type	Description
value	date, dateTime	The value from which to return the day of the month.

Returns

Type	Description
int	The day of the month.

DAYSFROMDURATION

This function returns the days portion of a duration value.

Syntax

DAYSFROMDURATION(value)

Argument	Type	Description
value	duration	The duration value from which to return the days.

Returns

Type	Description
long	The number of days in the duration.

DUR_TO_MILLIS

This function calculates the time in milliseconds from a duration value.

Syntax

DUR_TO_MILLIS(value)

Argument	Type	Description
value	duration	The duration value from which to calculate the time in milliseconds.

Returns

Type	Description
long	The number of milliseconds.

DUR_TO_USECS

This function calculates the time in microseconds from a duration value.

Syntax

DUR_TO_USECS(value)

Argument	Type	Description
value	duration	The duration value from which to calculate the time in microseconds.

Returns

Type	Description
long	The number of microseconds.

Example

SELECT (DUR_TO_USECS("PT2H11M48.376S"^^xsd:duration) as ?microseconds)

microseconds
------------------
7908376000
1 rows

FORMATDATETIME

This function converts a value into a string with the specified dateTime format.

Syntax

FORMATDATE(value, format)

Argument	Type	Description
value	long, double, date, time, dateTime	The value to convert to a string in the specified `format`.
format	string	The format to use for the resulting dateTime string. Graph Lakehouse supports `YYYY-MM-DDThh:mm:ss` format.

Returns

Type	Description
string	The dateTime as a string.

FORMATDURATION

This function converts a value into a string with the specified duration format.

Syntax

FORMATDURATION(value, format)

Argument	Type	Description
value	long, string, duration	The value to convert to a string in the specified `format`.
format	string	The format to use for the resulting duration string. Graph Lakehouse supports `PnYnMnDTnHnMnS` format.

Returns

Type	Description
string	The duration as a string.

HOURS

This function returns the hour portion of the given dateTime value.

Syntax

HOURS(value)

Argument	Type	Description
value	time, dateTime	The dateTime value from which to return the hours portion.

Returns

Type	Description
int	The hour.

MASKEDDATETIME

This function replaces the year, month, day, hour, minute, second, and millisecond values for the given date or dateTime value with the new date and time values that you specify.

Syntax

MASKEDDATETIME(value, year, month, day, hour, minute, second, milliseconds)

Argument	Type	Description
value	date, dateTime	The date or dateTime for which to replace the year, month, date, hour, minute, second, and milliseconds values.
year	int	The year to include in the resulting dateTime value.
month	int	The month to include in the resulting dateTime value.
day	int	The day to include in the resulting dateTime value.
hour	int	The hour to include in the resulting dateTime value.
minute	int	The minutes value to include in the resulting dateTime value.
second	int	The seconds value to include in the resulting dateTime value.
milliseconds	int	The milliseconds value to include in the resulting dateTime value.

Returns

Type	Description
dateTime	The dateTime value with the specified input values.

MILLIS_TO_DUR

This function converts milliseconds to a duration value.

Syntax

MILLIS_TO_DUR(value)

Argument	Type	Description
value	long, double, duration, string	The number of milliseconds.

Returns

Type	Description
duration	The duration value.

MINUTES

This function returns the minutes portion of the given time or dateTime value.

Syntax

MINUTES(value)

Argument	Type	Description
value	time, dateTime	The value from which to return the minutes portion.

Returns

Type	Description
int	The minutes portion of the input value.

MONTH

This function returns the month portion of the given date or dateTime value.

Syntax

MONTH(value)

Argument	Type	Description
value	date, dateTime	The value from which to return the month portion.

Returns

Type	Description
int	The month number.

Example

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 <http://anzograph.com/tickit>
WHERE {
  { SELECT ?eventid (MONTH(?eventtime) AS ?month)
    WHERE {
      ?eventid <http://anzograph.com/tickit/starttime> ?eventtime .
      ?sale <http://anzograph.com/tickit/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

NOW

This function returns the current server date and time.

Syntax

NOW()

Returns

Type	Description
dateTime	The current server date and time.

NOWMILLIS

This function returns the current server date and time in epoch milliseconds.

Syntax

NOWMILLIS()

Returns

Type	Description
long	The current server date and time in milliseconds.

PARSEDATE

This function attempts to convert the given string to a date, time, or dateTime value.

Syntax

PARSEDATE(value [, output_type ])

Argument	Type	Description
value	string	The string or plain literal value to convert to a date, time, or dateTime.
output_type	URI	An optional URI (`xsd:date`, `xsd:time`, or `xsd:dateTime`) that specifies the type of value to return. If `output_type` is not specified, dateTime is returned.

Returns

Type	Description
date, time, or dateTime	The conversion of the string to the desired type.

SECONDS_DBL

This function returns the seconds portion of the given time or dateTime value.

Syntax

SECONDS_DBL(value)

Argument	Type	Description
value	time, dateTime	The value from which to return the seconds portion.

Returns

Type	Description
double	The seconds portion of the input value.

TIME

This function returns an xsd:time value based on the specified hour, minute, and second values.

Syntax

TIME(hour, minute, second)

Argument	Type	Description
hour	long	A number that represents the hour.
minute	long	A number hat represents the minute.
second	long, double	A number that represents the seconds.

Returns

Type	Description
time	The time according to the input values.

TIMEZONE

This function returns the timezone part of a dateTime value as a duration.

Syntax

TIMEZONE(value)

Argument	Type	Description
value	dateTime	The value from which to retrieve the timezone.

Returns

Type	Description
duration	The timezone.

TODAY

This function returns today's date based on the server date.

Syntax

TODAY()

Returns

Type	Description
date	Today's date according to the server.

TOMILLIS

This function converts a date or dateTime value to the number of milliseconds.

Syntax

TOMILLIS(value)

Argument	Type	Description
value	date, dateTime	The value to convert to milliseconds.

Returns

Type	Description
long	The number of milliseconds.

TZ

This function returns the timezone part of a dateTime value as a string.

Syntax

TZ(value)

Argument	Type	Description
value	dateTime	The value from which to retrieve the timezone.

Returns

Type	Description
string	The timezone.

USECS_TO_DUR

This function converts a number of microseconds in long, duration, or string format to a duration value.

Syntax

USECS_TO_DUR(value)

Argument	Type	Description
value	long, duration, string	The microseconds value to convert to a duration.

Returns

Type	Description
duration	The input value as a duration.

Example

SELECT (USECS_TO_DUR(76555373888) as ?duration)

duration
----------------------
PT21H15M55.373888S
1 rows

WEEKDAY

This function returns the day of the week from a date or dateTime value.

Syntax

WEEKDAY(value [, day_number_start ])

Argument Type Description

value date, dateTime The date or dateTime value from which to return the day of the week.

Argument	Type	Description
value	date, dateTime	The date or dateTime value from which to return the day of the week.
day_number_start	long	An optional value of 1, 2, or 3 that defines how the days of the week are represented as numbers. 1 means Sunday is day 1. Saturday is day 7. 2 means Monday is day 1. Sunday is day 7. 3 means Monday is day 0. Sunday is day 6. If `day_number_start` is not specified, the default value is 1.

day_number_start

long

An optional value of 1, 2, or 3 that defines how the days of the week are represented as numbers.

1 means Sunday is day 1. Saturday is day 7.
2 means Monday is day 1. Sunday is day 7.
3 means Monday is day 0. Sunday is day 6.

If day_number_start is not specified, the default value is 1.

Returns

Type	Description
int	The day of the week from the input values.

WEEKNUM

This function returns the week of the year in which the given date or dateTime occurs.

Syntax

WEEKNUM(value [, day_week_begins ])

Argument Type Description

value date, dateTime The date or dateTime value from which to return the week number.

Argument	Type	Description
value	date, dateTime	The date or dateTime value from which to return the week number.
day_week_begins	long	An optional value of 1 or 2 that defines which day the weeks start on. 1 means a new week starts on Sunday. 2 means a new week starts on Monday. If `day_week_begins` is not specified, the default value is 1.

day_week_begins

long

An optional value of 1 or 2 that defines which day the weeks start on.

1 means a new week starts on Sunday.
2 means a new week starts on Monday.

If day_week_begins is not specified, the default value is 1.

Returns

Type	Description
int	The week of the year the input value falls in.

YEAR

This function returns the year portion of the given dateTime value.

Syntax

YEAR(value)

Argument	Type	Description
value	dateTime	The dateTime value to return the year from.

Returns

Type	Description
int	The year portion of the input values.

Example

The example below uses the NOW and YEAR functions to calculate the approximate ages of 10 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 <http://anzograph.com/tickit> 
WHERE {
  ?person <http://anzograph.com/tickit/birthday> ?birthdate .
  BIND(xsd:dateTime(NOW()) AS ?date)
}
ORDER BY ?person
LIMIT 10

person                                  | age
----------------------------------------+-----
http://anzograph.com/tickit/person1     |  55
http://anzograph.com/tickit/person10    |  75
http://anzograph.com/tickit/person100   |  32
http://anzograph.com/tickit/person1000  |  38
http://anzograph.com/tickit/person10000 |  77
http://anzograph.com/tickit/person10001 |  27
http://anzograph.com/tickit/person10002 |  75
http://anzograph.com/tickit/person10003 |  69
http://anzograph.com/tickit/person10004 |  50
http://anzograph.com/tickit/person10005 |  72
10 rows

YEARDAY

This function returns the day of the year from the specified date or dateTime value.

Syntax

YEARDAY(value)

Argument	Type	Description
value	date, dateTime	The value to return the day of the year from.

Returns

Type	Description
int	The day of the year.