Skip to content
This site is for a previous version of RelationalAI.

Time-Related Data Types

Rel has support for time-related data types. Specifically, it comes with the data types Date and DateTime. The Date data type can only be used for dates whereas DateTime is a timestamp and includes date, time, and time zone information.

Date properties, such as date_year, do not require a time zone. You can think of Date as a unit of time, counted out day by day.

By contrast, DateTime properties, such as datetime_year, do require a time zone. This is because you can interpret the 24-hour time component as relative to a time zone, giving different results for the year/month/day/hour/minute in some cases.

Parts of the DateTime value, specifically the seconds, are not time zone dependent. As a consequence, the relation datetime_second requires no time zone information.


Calendar date.


Date constants can be specified directly in YYYY-MM-DD format:

// read query
def output = {1776-07-04; 2020-02-29}

The utility parse_date[string, format] parses a string into a date, using the same formats accepted by the Julia language (opens in a new tab).

Date values are also generated when loading a CSV file with a column that has been specified for containing dates. See CSV Import for details.

Type Relation: Date(x)

Date(x) tests whether x has the data type Date.

// read query
def d = 2021-12-14
// The Date relation is contained in the built-in std::datetime module.
from ::std::datetime import Date
ic { Date(d) }

Noteworthy Operations


See also:


// read query
def d = parse_date["1616-4-23", "Y-m-d"]
def output:tuple = date_year[d], date_month[d], date_day[d]
def output:monthname = date_monthname[d]
def output:dayname = date_dayname[d]
def output:week = date_week[d]
def output:dayofyear = date_dayofyear[d]
def output:dayofweek = date_dayofweek[d]

To compute the number of days between two dates:

// read query
def d1 = parse_date["2014-1-29", "Y-m-d"]
def d2 = parse_date["2014-2-28", "Y-m-d"]
from ::std::datetime import Day
def output = x : date_add[d1, x] = d2 and Day(x)


A point in time, time zone agnostic, with nanosecond resolution. When working with datetimes, do not consider time zones or leap seconds.


DateTime types can be specified directly as YYYY-MM-DDThh:mm:ss<timezone>, where <timezone> is Z, or + or - followed by hh:mm. For more details on time zones, see List of TZ Database Time Zones (opens in a new tab).

For example:

// read query
def output = {

They can be parsed from strings with parse_datetime[string, format].

// read query
def output = {
    parse_datetime["2018-06-12 13:00 +00:00", "YYYY-mm-dd HH:MM zzzz"];
    parse_datetime["2018-03-11 01:00 America/New_York", "Y-m-d H:M Z"]

Currently, not all ISO DateTime formats are supported.

For instance, if your DateTime is specified as y-m-dTH:M:S.sZ, some workaround is needed:

// read query
def date_string = "2022-01-02T18:07:27.963Z"
def date_trimmed = substring[date_string, 1, num_chars[date_string]-1]
def output = parse_datetime[date_trimmed, "y-m-dTH:M:S.s"]

In this example, the time zone Z needs to be removed before parsing (y-m-dTH:M:S.s format is allowed).

Type Relation: DateTime(x)

DateTime(x) tests whether x has the data type DateTime.

// read query
def dt = 2021-10-12T01:22:31+10:00
// The DateTime relation is contained in the built-in std::datetime module.
ic { std::datetime::DateTime(dt) }

Noteworthy Operations


// read query
def dt = parse_datetime["2021-01-01 01:00:00", "Y-m-d H:M:S"]
def output:one = datetime_year[dt, "Europe/Berlin"]
def output:two = datetime_year[dt, "America/New_York"]
def output:three = datetime_year[dt, "-03:00"]

Note that the resolution of datetime_now is milliseconds, which will be sufficient for most use cases:

// read query
def datetime_to_milliseconds(datetime, v) =
    datetime_to_nanoseconds(datetime, ns) and
    v = trunc_divide[ns, 10^6]
    from ns
def dt = unix_epoch + ^Day[1]
def output = datetime_to_milliseconds[dt]
ic datetime_ic { output = 24 * 60 * 60 * 1000 }

Date and Time Periods


Time period: ^Nanosecond[n], ^Microsecond[n], ^Millisecond[n], ^Second[n], ^Minute[n], and ^Hour[n].

Date period: ^Day[n], ^Week[n], ^Month[n], and ^Year[n].

For all constructors the variable n is an integer indicating how many multiples of the time/date periods are requested:

// read query
def output {^Second[100]; ^Hour[2]; ^Day[1]; ^Month[24]}

Note that all of time and date period constructors are contained in the built-in std::datetime module.

Type Relations

The following unary relations check whether a variable has the specific date-period-like or time-period-like data type.

Time period: std::datetime::Nanosecond(x), std::datetime::Microsecond(x), std::datetime::Millisecond(x), std::datetime::Second(x), std::datetime::Minute(x), and std::datetime::Hour(x).

// read query
def R {^Nanosecond[100]; ^Microsecond[10]; ^Minute[1]}
from ::std::datetime import Nanosecond, Minute
def output(x): R(x) and (Nanosecond(x) or Minute(x))

Note that all of time and date period type relations are contained in the built-in std::datetime module.

Date period: std::datetime::Day(x), std::datetime::Week(x), std::datetime::Month(x), and std::datetime::Year(x).

// read query
def R {^Day[100]; ^Week[52]; ^Month[1]; ^Year[2000]}
from ::std::datetime import Week, Year
def output(x) = R(x) and (Week(x) or Year(x))

Noteworthy Operations


Time periods can be added or subtracted from Date and DateTime. Rel follows the Julia period-arithmetic conventions (opens in a new tab). Adding Year, Day, and Second periods to a Date or DateTime is straightforward, since these periods have a well-defined time duration.

Adding Month periods, on the other hand, is different. It usually advances to the same date (and time) in the corresponding new month, so that adding months to the 10th of a month always results in the 10th of another month. For leap years, however, you can choose the 28th, as adding months to the 29th would give a nonexisting date:

// read query
def output:jan = parse_date["2014-1-29", "Y-m-d"] // Not a leap year
def output:feb = output:jan + ^Month[1]
def output:mar = output:feb + ^Month[2]

You can compare this to:

// read query
def output:jan = parse_date["2016-1-29", "Y-m-d"] // A leap year
def output:feb = output:jan + ^Month[1]
def output:mar = output:feb + ^Month[2]

Note that adding day and month periods is therefore not associative:

// read query
def d = parse_date["2014-1-29", "Y-m-d"]
def output:one = (d + ^Day[1]) + ^Month[1]
def output:two = (d + ^Month[1]) + ^Day[1]

Next: Value Types

Was this doc helpful?