datetime()#

relationalai.std.dates
#datetime(
    year: int|Producer,
    month: int|Producer,
    day: int|Producer,
    hour: int|Producer = 0,
    minute: int|Producer = 0,
    second: int|Producer = 0,
    millisecond: int|Producer = 0,
    tz: tzinfo|str|Producer = "UTC"
) -> Expression

Constructs a UTC datetime value. If one of the components is a Producer object, then datetime() also acts as a filter and removes non-integer values from the producer. If tz is specified, the datetime is converted to UTC from the specified timezone or offset string. Must be called in a rule or query context. Alternate constructors include datetime.strptime() and datetime.fromdate(). To construct a date value, use the date() function.

NOTE

RAI datetimes are stored as UTC values with nanosecond precision. While you may only create datetimes with millisecond precision, they can be manipulated at the nanosecond level.

Parameters#

NameTypeDescription
yearint or ProducerThe year component.
monthint or ProducerThe month component.
dayint or ProducerThe day component.
hourint or ProducerOptional hour component (Default: 0).
minuteint or ProducerOptional minute component (Default: 0).
secondint or ProducerOptional second component (Default: 0).
millisecondint or ProducerOptional millisecond component (Default: 0).
tztzinfo or str or ProducerOptional timezone string (e.g., "America/New_York"), offset string (e.g., "+0600"), or Python tzinfo object. Refer to the timezone database for a list of valid timezone indentifiers. (Default: "UTC").

Returns#

An Expression object.

Example#

Use datetime() to construct a datetime value by specifying the year, month, day, hour, minute, second, and millisecond components:

#import relationalai as rai
from relationalai.std import dates


model = rai.Model("MyModel")
Person = model.Type("Person")

# Define some people with a born_at datetime property.
with model.rule():
    Person.add(id=1).set(name="Bob", born_at=dates.datetime(2021, 1, 1, 12))
    Person.add(id=2).set(name="Alice", born_at=dates.datetime(1990, 5, 23, 10, tz="America/New_York"))

If you specify a timezone other than UTC, the datetime value is converted from the specified timezone to UTC:

#with model.query() as select:
    person = Person()
    response = select(person.name, person.born_at)

print(response.results)
#     name             born_at
# 0  Alice 1990-05-23 14:00:00  <-- 10:00:00 in America/New_York is 14:00:00 in UTC
# 1    Bob 2021-01-01 12:00:00

You can set an additional property to store the original timezone of the datetime value so that it can be recalled later if needed:

#with model.rule():
    Person(name="Alice").set(timezone="America/New_York")
    Person(name="Bob").set(timezone="UTC")

Use the + and - operators to add and subtract time periods, like days() or hours(), from datetime values:

#from relationalai.std import alias

with model.query() as select:
    person = Person()
    # Add 3 hours to each person's born_at datetime.
    new_datetime = person.born_at + dates.hours(3)
    response = select(person.name, person.born_at, alias(new_datetime, "new_datetime"))

print(response.results)
#     name             born_at        new_datetime
# 0  Alice 1990-05-23 14:00:00 1990-05-23 17:00:00
# 1    Bob 2021-01-01 12:00:00 2021-01-01 15:00:00

with model.query() as select:
    person = Person()
    # Subtract 1 day from each person's born_at datetime.
    new_datetime = person.born_at - dates.days(1)
    response = select(person.name, person.born_at, alias(new_datetime, "new_datetime"))

print(response.results)
#     name             born_at        new_datetime
# 0  Alice 1990-05-23 14:00:00 1990-05-22 14:00:00
# 1    Bob 2021-01-01 12:00:00 2020-12-31 12:00:00

The difference between two datetimes can be calculated using the - operator and is returned as a period of nanoseconds:

#with model.query() as select:
    person1, person2 = Person(), Person()
    person1.born_at > person2.born_at
    difference = person1.born_at - person2.born_at
    response = select(person1.name, person2.name, alias(difference, "difference"))

print(response.results)
#   name  name2    difference
# 0  Bob  Alice  966031200000

Keep in mind that the difference between two datetimes is not an integer. In particular, you can’t do arithmetic on the difference directly. For example, if you want to convert the difference from nanosecond to years, you must first extract the integer value from the time period as follows:

#from relationalai.std import create_var

with model.query() as select:
    person1, person2 = Person(), Person()
    person1.born_at > person2.born_at
    # Get the integer difference in nanoseconds between the two datetimes.
    diff_ns = create_var()
    dates.nanoseconds(diff_ns) == person1.born_at - person2.born_at
    # Convert the difference in nanoseconds to years.
    diff_years = diff_ns / 1e9 / 60 / 60 / 24 / 365
    response = select(person1.name, person2.name, alias(diff_years, "diff_years"))

print(response.results)
#   name  name2  diff_years
# 0  Bob  Alice   30.632648

You can extract parts of a datetime value using functions like hour() or minute():

#with model.query() as select:
    alice = Person(name="Alice")
    # Get the UTC hour of Alice's birth.
    hour_utc = dates.hour(alice.born_at)
    # Get the hour of Alice's birth in her local timezone.
    hour_tz = dates.hour(alice.born_at, tz=alice.born_at_tz)
    response = select(
        alice.born_at,
        alias(hour_utc, "birth_hour_utc"),
        alias(hour_tz, "birth_hour_tz"),
    )

print(response.results)
#               born_at  birth_hour_utc  birth_hour_tz
# 0 1990-05-23 14:00:00              14             10

See Also#