Temporal expressions

Dates, times, timestamps and intervals.

TimestampValue

TimestampValue(self, arg)

Attributes

Name Description
add Add an interval to a timestamp.
radd Add an interval to a timestamp.
sub Subtract a timestamp or an interval from a timestamp.
day_of_week A namespace of methods for extracting day of week information.

Methods

Name Description
between Check if the expr falls between lower and upper, inclusive.
bucket Truncate the timestamp to buckets of a specified interval.
date Return the date component of the expression.
day Extract the day component.
day_of_year Extract the day of the year component.
delta Compute the number of parts between two timestamps.
epoch_seconds Extract UNIX epoch in seconds.
hour Extract the hour component.
microsecond Extract the microsecond component.
millisecond Extract the millisecond component.
minute Extract the minute component.
month Extract the month component.
quarter Extract the quarter component.
second Extract the second component.
strftime Format a timestamp according to format_str.
time Return the time component of the expression.
truncate Truncate timestamp expression to units of unit.
week_of_year Extract the week of the year component.
year Extract the year component.

between

between(lower, upper, timezone=None)

Check if the expr falls between lower and upper, inclusive.

Adjusts according to timezone if provided.

Parameters

Name Type Description Default
lower str | datetime.time | TimeValue Lower bound required
upper str | datetime.time | TimeValue Upper bound required
timezone str | None Time zone None

Returns

Type Description
BooleanValue Whether self is between lower and upper, adjusting timezone as needed.

bucket

bucket(interval=None, *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None, offset=None)

Truncate the timestamp to buckets of a specified interval.

This is similar to truncate, but supports truncating to arbitrary intervals rather than a single unit. Buckets are computed as fixed intervals starting from the UNIX epoch. This origin may be offset by specifying offset.

Parameters

Name Type Description Default
interval Any The bucket width as an interval. Alternatively may be specified via component keyword arguments. None
years int | None Number of years None
quarters int | None Number of quarters None
months int | None Number of months None
weeks int | None Number of weeks None
days int | None Number of days None
hours int | None Number of hours None
minutes int | None Number of minutes None
seconds int | None Number of seconds None
milliseconds int | None Number of milliseconds None
microseconds int | None Number of microseconds None
nanoseconds int | None Number of nanoseconds None
offset Any An interval to use to offset the start of the bucket. None

Returns

Type Description
TimestampValue The start of the bucket as a timestamp.

Examples

>>> import ibis
>>> from ibis import _
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     [
...         ("2020-04-15 08:04:00", 1),
...         ("2020-04-15 08:06:00", 2),
...         ("2020-04-15 08:09:00", 3),
...         ("2020-04-15 08:11:00", 4),
...     ],
...     columns=["ts", "val"],
... ).cast({"ts": "timestamp"})

Bucket the data into 5 minute wide buckets:

>>> t.ts.bucket(minutes=5)
┏━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ TimestampBucket(ts, 5m) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp               │
├─────────────────────────┤
│ 2020-04-15 08:00:00     │
│ 2020-04-15 08:05:00     │
│ 2020-04-15 08:05:00     │
│ 2020-04-15 08:10:00     │
└─────────────────────────┘

Bucket the data into 5 minute wide buckets, offset by 2 minutes:

>>> t.ts.bucket(minutes=5, offset=ibis.interval(minutes=2))
┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ TimestampBucket(ts, 5m, 2m) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp                   │
├─────────────────────────────┤
│ 2020-04-15 08:02:00         │
│ 2020-04-15 08:02:00         │
│ 2020-04-15 08:07:00         │
│ 2020-04-15 08:07:00         │
└─────────────────────────────┘

One common use of timestamp bucketing is computing statistics per bucket. Here we compute the mean of val across 5 minute intervals:

>>> mean_by_bucket = (
...     t.group_by(t.ts.bucket(minutes=5).name("bucket"))
...     .agg(mean=_.val.mean())
...     .order_by("bucket")
... )
>>> mean_by_bucket
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
┃ bucket               mean    ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
│ timestampfloat64 │
├─────────────────────┼─────────┤
│ 2020-04-15 08:00:001.0 │
│ 2020-04-15 08:05:002.5 │
│ 2020-04-15 08:10:004.0 │
└─────────────────────┴─────────┘

date

date()

Return the date component of the expression.

Returns

Type Description
DateValue The date component of self

day

day()

Extract the day component.

day_of_year

day_of_year()

Extract the day of the year component.

delta

delta(other, part)

Compute the number of parts between two timestamps.

The order of operands matches standard subtraction

The second argument is subtracted from the first.

Parameters

Name Type Description Default
other datetime.datetime | Value[dt.Timestamp] A timestamp expression required
part Literal[‘year’, ‘quarter’, ‘month’, ‘week’, ‘day’, ‘hour’, ‘minute’, ‘second’, ‘millisecond’, ‘microsecond’, ‘nanosecond’] | Value[dt.String] The unit of time to compute the difference in required

Returns

Type Description
IntegerValue The number of parts between self and other

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> start = ibis.time("01:58:00")
>>> end = ibis.time("23:59:59")
>>> end.delta(start, "hour")

22
>>> data = '''tpep_pickup_datetime,tpep_dropoff_datetime
... 2016-02-01T00:23:56,2016-02-01T00:42:28
... 2016-02-01T00:12:14,2016-02-01T00:21:41
... 2016-02-01T00:43:24,2016-02-01T00:46:14
... 2016-02-01T00:55:11,2016-02-01T01:24:34
... 2016-02-01T00:11:13,2016-02-01T00:16:59'''
>>> with open("/tmp/triptimes.csv", "w") as f:
...     nbytes = f.write(data)  # nbytes is unused
>>> taxi = ibis.read_csv("/tmp/triptimes.csv")
>>> ride_duration = taxi.tpep_dropoff_datetime.delta(
...     taxi.tpep_pickup_datetime, "minute"
... ).name("ride_minutes")
>>> ride_duration
┏━━━━━━━━━━━━━━┓
┃ ride_minutes ┃
┡━━━━━━━━━━━━━━┩
│ int64        │
├──────────────┤
│           19 │
│            9 │
│            3 │
│           29 │
│            5 │
└──────────────┘

epoch_seconds

epoch_seconds()

Extract UNIX epoch in seconds.

hour

hour()

Extract the hour component.

microsecond

microsecond()

Extract the microsecond component.

millisecond

millisecond()

Extract the millisecond component.

minute

minute()

Extract the minute component.

month

month()

Extract the month component.

quarter

quarter()

Extract the quarter component.

second

second()

Extract the second component.

strftime

strftime(format_str)

Format a timestamp according to format_str.

Format string may depend on the backend, but we try to conform to ANSI strftime.

Parameters

Name Type Description Default
format_str str strftime format string required

Returns

Type Description
StringValue Formatted version of arg

time

time()

Return the time component of the expression.

Returns

Type Description
TimeValue The time component of self

truncate

truncate(unit)

Truncate timestamp expression to units of unit.

Parameters

Name Type Description Default
unit Literal[‘Y’, ‘Q’, ‘M’, ‘W’, ‘D’, ‘h’, ‘m’, ‘s’, ‘ms’, ‘us’, ‘ns’] Unit to truncate to required

Returns

Type Description
TimestampValue Truncated timestamp expression

week_of_year

week_of_year()

Extract the week of the year component.

year

year()

Extract the year component.

DateValue

DateValue(self, arg)

Attributes

Name Description
add Add an interval to a date.
radd Add an interval to a date.
sub Subtract a date or an interval from a date.
day_of_week A namespace of methods for extracting day of week information.

Methods

Name Description
day Extract the day component.
day_of_year Extract the day of the year component.
epoch_seconds Extract UNIX epoch in seconds.
month Extract the month component.
quarter Extract the quarter component.
strftime Format a date according to format_str.
truncate Truncate date expression to units of unit.
week_of_year Extract the week of the year component.
year Extract the year component.

day

day()

Extract the day component.

day_of_year

day_of_year()

Extract the day of the year component.

epoch_seconds

epoch_seconds()

Extract UNIX epoch in seconds.

month

month()

Extract the month component.

quarter

quarter()

Extract the quarter component.

strftime

strftime(format_str)

Format a date according to format_str.

Format string may depend on the backend, but we try to conform to ANSI strftime.

Parameters

Name Type Description Default
format_str str strftime format string required

Returns

Type Description
StringValue Formatted version of arg

truncate

truncate(unit)

Truncate date expression to units of unit.

Parameters

Name Type Description Default
unit Literal[‘Y’, ‘Q’, ‘M’, ‘W’, ‘D’] Unit to truncate arg to required

Returns

Type Description
DateValue Truncated date value expression

week_of_year

week_of_year()

Extract the week of the year component.

year

year()

Extract the year component.

TimeValue

TimeValue(self, arg)

Attributes

Name Description
add Add an interval to a time expression.
radd Add an interval to a time expression.
sub Subtract a time or an interval from a time expression.

Methods

Name Description
between Check if the expr falls between lower and upper, inclusive.
hour Extract the hour component.
microsecond Extract the microsecond component.
millisecond Extract the millisecond component.
minute Extract the minute component.
second Extract the second component.
strftime Format a time according to format_str.
time Return the time component of the expression.
truncate Truncate the expression to a time expression in units of unit.

between

between(lower, upper, timezone=None)

Check if the expr falls between lower and upper, inclusive.

Adjusts according to timezone if provided.

Parameters

Name Type Description Default
lower str | datetime.time | TimeValue Lower bound required
upper str | datetime.time | TimeValue Upper bound required
timezone str | None Time zone None

Returns

Type Description
BooleanValue Whether self is between lower and upper, adjusting timezone as needed.

hour

hour()

Extract the hour component.

microsecond

microsecond()

Extract the microsecond component.

millisecond

millisecond()

Extract the millisecond component.

minute

minute()

Extract the minute component.

second

second()

Extract the second component.

strftime

strftime(format_str)

Format a time according to format_str.

Format string may depend on the backend, but we try to conform to ANSI strftime.

Parameters

Name Type Description Default
format_str str strftime format string required

Returns

Type Description
StringValue Formatted version of arg

time

time()

Return the time component of the expression.

Returns

Type Description
TimeValue The time component of self

truncate

truncate(unit)

Truncate the expression to a time expression in units of unit.

Commonly used for time series resampling.

Parameters

Name Type Description Default
unit Literal[‘h’, ‘m’, ‘s’, ‘ms’, ‘us’, ‘ns’] The unit to truncate to required

Returns

Type Description
TimeValue self truncated to unit

IntervalValue

IntervalValue(self, arg)

Attributes

Name Description
days Extract the number of days from an interval.
hours Extract the number of hours from an interval.
microseconds Extract the number of microseconds from an interval.
milliseconds Extract the number of milliseconds from an interval.
minutes Extract the number of minutes from an interval.
months Extract the number of months from an interval.
nanoseconds Extract the number of nanoseconds from an interval.
quarters Extract the number of quarters from an interval.
seconds Extract the number of seconds from an interval.
weeks Extract the number of weeks from an interval.
years Extract the number of years from an interval.

Methods

Name Description
negate Negate an interval expression.
to_unit Convert this interval to units of target_unit.

negate

negate()

Negate an interval expression.

Returns

Type Description
IntervalValue A negated interval value expression

to_unit

to_unit(target_unit)

Convert this interval to units of target_unit.

DayOfWeek

DayOfWeek(self, expr)

A namespace of methods for extracting day of week information.

Methods

Name Description
full_name Get the name of the day of the week.
index Get the index of the day of the week.

full_name

full_name()

Get the name of the day of the week.

Returns

Type Description
StringValue The name of the day of the week

index

index()

Get the index of the day of the week.

Ibis follows the pandas convention for day numbering: Monday = 0 and Sunday = 6.

Returns

Type Description
IntegerValue The index of the day of the week.

now

ibis.now()

Return an expression that will compute the current timestamp.

Returns

Type Description
TimestampScalar An expression representing the current timestamp.

date

ibis.date(value_or_year, month=None, day=None, /)

Construct a date scalar or column.

Parameters

Name Type Description Default
value_or_year Either a string value or datetime.date to coerce to a date, or an integral value representing the date year component. required
month The date month component; required if value_or_year is a year. None
day The date day component; required if value_or_year is a year. None

Returns

Type Description
DateValue A date expression

Examples

>>> import ibis
>>> ibis.options.interactive = True

Create a date scalar from a string

>>> ibis.date("2023-01-02")

datetime.date(2023, 1, 2)

Create a date scalar from year, month, and day

>>> ibis.date(2023, 1, 2)

datetime.date(2023, 1, 2)

Create a date column from year, month, and day

>>> t = ibis.memtable(dict(year=[2001, 2002], month=[1, 3], day=[2, 4]))
>>> ibis.date(t.year, t.month, t.day).name("my_date")
┏━━━━━━━━━━━━┓
┃ my_date    ┃
┡━━━━━━━━━━━━┩
│ date       │
├────────────┤
│ 2001-01-02 │
│ 2002-03-04 │
└────────────┘

time

ibis.time(value_or_hour, minute=None, second=None, /)

Return a time literal if value is coercible to a time.

Parameters

Name Type Description Default
value_or_hour Either a string value or datetime.time to coerce to a time, or an integral value representing the time hour component. required
minute The time minute component; required if value_or_hour is an hour. None
second The time second component; required if value_or_hour is an hour. None

Returns

Type Description
TimeValue A time expression

Examples

>>> import ibis
>>> ibis.options.interactive = True

Create a time scalar from a string

>>> ibis.time("01:02:03")

datetime.time(1, 2, 3)

Create a time scalar from hour, minute, and second

>>> ibis.time(1, 2, 3)

datetime.time(1, 2, 3)

Create a time column from hour, minute, and second

>>> t = ibis.memtable({"h": [1, 4], "m": [2, 5], "s": [3, 6]})
>>> ibis.time(t.h, t.m, t.s).name("time")
┏━━━━━━━━━━┓
┃ time     ┃
┡━━━━━━━━━━┩
│ time     │
├──────────┤
│ 01:02:03 │
│ 04:05:06 │
└──────────┘

timestamp

ibis.timestamp(value_or_year, month=None, day=None, hour=None, minute=None, second=None, /, timezone=None)

Construct a timestamp scalar or column.

Parameters

Name Type Description Default
value_or_year Either a string value or datetime.datetime to coerce to a timestamp, or an integral value representing the timestamp year component. required
month The timestamp month component; required if value_or_year is a year. None
day The timestamp day component; required if value_or_year is a year. None
hour The timestamp hour component; required if value_or_year is a year. None
minute The timestamp minute component; required if value_or_year is a year. None
second The timestamp second component; required if value_or_year is a year. None
timezone The timezone name, or none for a timezone-naive timestamp. None

Returns

Type Description
TimestampValue A timestamp expression

Examples

>>> import ibis
>>> ibis.options.interactive = True

Create a timestamp scalar from a string

>>> ibis.timestamp("2023-01-02T03:04:05")

Timestamp('2023-01-02 03:04:05')

Create a timestamp scalar from components

>>> ibis.timestamp(2023, 1, 2, 3, 4, 5)

Timestamp('2023-01-02 03:04:05')

Create a timestamp column from components

>>> t = ibis.memtable({"y": [2001, 2002], "m": [1, 4], "d": [2, 5], "h": [3, 6]})
>>> ibis.timestamp(t.y, t.m, t.d, t.h, 0, 0).name("timestamp")
┏━━━━━━━━━━━━━━━━━━━━━┓
┃ timestamp           ┃
┡━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │
├─────────────────────┤
│ 2001-01-02 03:00:00 │
│ 2002-04-05 06:00:00 │
└─────────────────────┘

interval

ibis.interval(value=None, unit='s', *, years=None, quarters=None, months=None, weeks=None, days=None, hours=None, minutes=None, seconds=None, milliseconds=None, microseconds=None, nanoseconds=None)

Return an interval literal expression.

Parameters

Name Type Description Default
value int | datetime.timedelta | None Interval value. None
unit str Unit of value 's'
years int | None Number of years None
quarters int | None Number of quarters None
months int | None Number of months None
weeks int | None Number of weeks None
days int | None Number of days None
hours int | None Number of hours None
minutes int | None Number of minutes None
seconds int | None Number of seconds None
milliseconds int | None Number of milliseconds None
microseconds int | None Number of microseconds None
nanoseconds int | None Number of nanoseconds None

Returns

Type Description
IntervalScalar An interval expression
Back to top