Skip to content
REL
REFERENCE
Data Types
Time

Data Types: 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.

Date

Calendar date.

Construction

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

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

// query
 
def d = 2021-12-14
 
ic { Date(d) }

Noteworthy Operations

Accessors:

See also:

Examples

// 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:

// query
 
def d1 = parse_date["2014-1-29", "Y-m-d"]
def d2 = parse_date["2014-2-28", "Y-m-d"]
 
def output = x : date_add[d1, x] = d2 and is_Day(x)

DateTime

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

Construction

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:

// query
 
def output = {
    2021-10-12T01:22:31+10:00;
    1955-11-12T22:04:00-08:00;
    1970-01-01T00:00:00Z
}

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

// 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:

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

// query
 
def dt = 2021-10-12T01:22:31+10:00
 
ic {DateTime(dt)}

Noteworthy Operations

Examples

// 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:

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

Date and Time Periods

Construction

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:

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

Type Relations

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

Time period: is_Nanosecond(x), is_Microsecond(x), is_Millisecond(x), is_Second(x), is_Minute(x), and is_Hour(x).

// query
 
def R = {Nanosecond[100]; Microsecond[10]; Minute[1]}
 
def output(x) = R(x) and (is_Nanosecond(x) or is_Minute(x))

Date period: is_Day(x), is_Week(x), is_Month(x), and is_Year(x).

// query
 
def R = {Day[100]; Week[52]; Month[1]; Year[2000]}
 
def output(x) = R(x) and (is_Week(x) or is_Year(x))

Noteworthy Operations

Examples

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:

// 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:

// 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:

// 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: Entity Data Types

Was this doc helpful?