Temporal expressions

Dates, times, timestamps and intervals.

TimestampValue

TimestampValue(arg)

A date and time, eg 2024-12-31 23:59:59.999999.

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 `part`s 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

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

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> lower = ibis.date(2024, 12, 30)
>>> upper = ibis.date(2025, 1, 1)
>>> ibis.date(2024, 12, 31).between(lower, upper)

┌──────┐
│ True │
└──────┘

>>> ibis.date(2020, 12, 31).between(lower, upper)

┌───────┐
│ False │
└───────┘

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

Name	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    ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
│ timestamp           │ float64 │
├─────────────────────┼─────────┤
│ 2020-04-15 08:00:00 │     1.0 │
│ 2020-04-15 08:05:00 │     2.5 │
│ 2020-04-15 08:10:00 │     4.0 │
└─────────────────────┴─────────┘

date

date()

Return the date component of the expression.

Returns

Name	Type	Description
	DateValue	The date component of `self`

day

day()

Extract the day component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).day()

┌────┐
│ 31 │
└────┘

day_of_year

day_of_year()

Extract the day of the year component.

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2023, 1, 1),
...             date(2023, 6, 17),
...             date(2023, 12, 31),
...             date(2024, 2, 29),
...             date(2024, 12, 31),
...         ]
...     },
... )
>>> t.date_col.day_of_year()

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ ExtractDayOfYear(date_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int32                      │
├────────────────────────────┤
│                          1 │
│                        168 │
│                        365 │
│                         60 │
│                        366 │
└────────────────────────────┘

delta

delta(other, /, *, unit)

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
unit	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

Name	Type	Description
	IntegerValue	The number of `part`s 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, unit="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, unit="minute"
... ).name("ride_minutes")
>>> ride_duration

┏━━━━━━━━━━━━━━┓
┃ ride_minutes ┃
┡━━━━━━━━━━━━━━┩
│ int64        │
├──────────────┤
│           19 │
│            9 │
│            3 │
│           29 │
│            5 │
└──────────────┘

epoch_seconds

epoch_seconds()

Extract UNIX epoch in seconds.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).epoch_seconds()

┌────────────┐
│ 1735603200 │
└────────────┘

hour

hour()

Extract the hour component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).hour()

┌────┐
│ 23 │
└────┘

microsecond

microsecond()

Extract the microsecond component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2024-12-31 23:59:59.999").microsecond()

┌────────┐
│ 999000 │
└────────┘

millisecond

millisecond()

Extract the millisecond component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2024-12-31 23:59:59.999").millisecond()

┌─────┐
│ 999 │
└─────┘

minute

minute()

Extract the minute component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).minute()

┌────┐
│ 59 │
└────┘

month

month()

Extract the month component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).month()

┌────┐
│ 12 │
└────┘

quarter

quarter()

Extract the quarter component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).quarter()

┌───┐
│ 4 │
└───┘

second

second()

Extract the second component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).second()

┌────┐
│ 59 │
└────┘

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

Name	Type	Description
	StringValue	Formatted version of `arg`

Examples

>>> from datetime import datetime
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "timestamp_col": [
...             datetime(2020, 10, 5, 8, 0, 0),
...             datetime(2020, 11, 10, 10, 2, 15),
...             datetime(2020, 12, 15, 12, 4, 30),
...         ]
...     },
... )

Return a string with the year and month.

>>> t.timestamp_col.strftime("%Y-%m")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Strftime(timestamp_col, '%Y-%m') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                           │
├──────────────────────────────────┤
│ 2020-10                          │
│ 2020-11                          │
│ 2020-12                          │
└──────────────────────────────────┘

Return a string with the month, day, and year.

>>> t.timestamp_col.strftime("%B %-d, %Y")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Strftime(timestamp_col, '%B %-d, %Y') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                                │
├───────────────────────────────────────┤
│ October 5, 2020                       │
│ November 10, 2020                     │
│ December 15, 2020                     │
└───────────────────────────────────────┘

Return a string with the month, day, year, hour, minute, and AM/PM.

>>> t.timestamp_col.strftime("%B %-d, %Y at %I:%M %p")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Strftime(timestamp_col, '%B %-d, %Y at %I:%M %p') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                                            │
├───────────────────────────────────────────────────┤
│ October 5, 2020 at 08:00 AM                       │
│ November 10, 2020 at 10:02 AM                     │
│ December 15, 2020 at 12:04 PM                     │
└───────────────────────────────────────────────────┘

time

time()

Return the time component of the expression.

Returns

Name	Type	Description
	TimeValue	The time component of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).time()

┌──────────┐
│ 23:59:59 │
└──────────┘

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

Name	Type	Description
	TimestampValue	Truncated timestamp expression

Examples

>>> from datetime import datetime
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "timestamp_col": [
...             datetime(2020, 1, 5, 8, 0, 0),
...             datetime(2020, 4, 10, 10, 2, 15),
...             datetime(2020, 7, 15, 12, 4, 30),
...             datetime(2020, 10, 20, 14, 6, 45),
...         ]
...     },
... )

Return timestamp columns truncated to the start of the year, quarter, and month.

>>> t.select(
...     year=t.timestamp_col.truncate("Y"),
...     quarter=t.timestamp_col.truncate("Q"),
...     month=t.timestamp_col.truncate("M"),
... )

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ year                ┃ quarter             ┃ month               ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ timestamp           │ timestamp           │
├─────────────────────┼─────────────────────┼─────────────────────┤
│ 2020-01-01 00:00:00 │ 2020-01-01 00:00:00 │ 2020-01-01 00:00:00 │
│ 2020-01-01 00:00:00 │ 2020-04-01 00:00:00 │ 2020-04-01 00:00:00 │
│ 2020-01-01 00:00:00 │ 2020-07-01 00:00:00 │ 2020-07-01 00:00:00 │
│ 2020-01-01 00:00:00 │ 2020-10-01 00:00:00 │ 2020-10-01 00:00:00 │
└─────────────────────┴─────────────────────┴─────────────────────┘

Return timestamp columns truncated to the start of the week and day.

>>> t.select(week=t.timestamp_col.truncate("W"), day=t.timestamp_col.truncate("D"))

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ week                ┃ day                 ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ timestamp           │
├─────────────────────┼─────────────────────┤
│ 2019-12-30 00:00:00 │ 2020-01-05 00:00:00 │
│ 2020-04-06 00:00:00 │ 2020-04-10 00:00:00 │
│ 2020-07-13 00:00:00 │ 2020-07-15 00:00:00 │
│ 2020-10-19 00:00:00 │ 2020-10-20 00:00:00 │
└─────────────────────┴─────────────────────┘

Return timestamp columns truncated to the start of the hour, minute, and second.

>>> t.select(
...     hour=t.timestamp_col.truncate("h"),
...     minute=t.timestamp_col.truncate("m"),
...     second=t.timestamp_col.truncate("s"),
... )

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ hour                ┃ minute              ┃ second              ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ timestamp           │ timestamp           │
├─────────────────────┼─────────────────────┼─────────────────────┤
│ 2020-01-05 08:00:00 │ 2020-01-05 08:00:00 │ 2020-01-05 08:00:00 │
│ 2020-04-10 10:00:00 │ 2020-04-10 10:02:00 │ 2020-04-10 10:02:15 │
│ 2020-07-15 12:00:00 │ 2020-07-15 12:04:00 │ 2020-07-15 12:04:30 │
│ 2020-10-20 14:00:00 │ 2020-10-20 14:06:00 │ 2020-10-20 14:06:45 │
└─────────────────────┴─────────────────────┴─────────────────────┘

week_of_year

week_of_year()

Extract the week of the year component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).week_of_year()

┌───┐
│ 1 │
└───┘

year

year()

Extract the year component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).year()

┌──────┐
│ 2024 │
└──────┘

DateValue

DateValue(arg)

A date (without time), eg 2024-12-31.

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.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).day()

┌────┐
│ 31 │
└────┘

day_of_year

day_of_year()

Extract the day of the year component.

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2023, 1, 1),
...             date(2023, 6, 17),
...             date(2023, 12, 31),
...             date(2024, 2, 29),
...             date(2024, 12, 31),
...         ]
...     },
... )
>>> t.date_col.day_of_year()

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ ExtractDayOfYear(date_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int32                      │
├────────────────────────────┤
│                          1 │
│                        168 │
│                        365 │
│                         60 │
│                        366 │
└────────────────────────────┘

epoch_seconds

epoch_seconds()

Extract UNIX epoch in seconds.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).epoch_seconds()

┌────────────┐
│ 1735603200 │
└────────────┘

month

month()

Extract the month component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).month()

┌────┐
│ 12 │
└────┘

quarter

quarter()

Extract the quarter component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).quarter()

┌───┐
│ 4 │
└───┘

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

Name	Type	Description
	StringValue	Formatted version of `arg`

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2020, 10, 5),
...             date(2020, 11, 10),
...             date(2020, 12, 15),
...         ]
...     },
... )

Return a string with the year and month.

>>> t.date_col.strftime("%Y-%m")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Strftime(date_col, '%Y-%m') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                      │
├─────────────────────────────┤
│ 2020-10                     │
│ 2020-11                     │
│ 2020-12                     │
└─────────────────────────────┘

Return a string with the month name, day, and year.

>>> t.date_col.strftime("%B %-d, %Y")

┏━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ Strftime(date_col, '%B %-d, %Y') ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                           │
├──────────────────────────────────┤
│ October 5, 2020                  │
│ November 10, 2020                │
│ December 15, 2020                │
└──────────────────────────────────┘

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

Name	Type	Description
	DateValue	Truncated date value expression

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2020, 1, 5),
...             date(2020, 4, 10),
...             date(2020, 7, 15),
...             date(2020, 10, 20),
...         ]
...     },
... )

Return date columns truncated to the start of the year, quarter, month, and week.

>>> t.select(
...     year=t.date_col.truncate("Y"),
...     quarter=t.date_col.truncate("Q"),
...     month=t.date_col.truncate("M"),
...     week=t.date_col.truncate("W"),
... )

┏━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━━━━┓
┃ year       ┃ quarter    ┃ month      ┃ week       ┃
┡━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━━━━┩
│ date       │ date       │ date       │ date       │
├────────────┼────────────┼────────────┼────────────┤
│ 2020-01-01 │ 2020-01-01 │ 2020-01-01 │ 2019-12-30 │
│ 2020-01-01 │ 2020-04-01 │ 2020-04-01 │ 2020-04-06 │
│ 2020-01-01 │ 2020-07-01 │ 2020-07-01 │ 2020-07-13 │
│ 2020-01-01 │ 2020-10-01 │ 2020-10-01 │ 2020-10-19 │
└────────────┴────────────┴────────────┴────────────┘

week_of_year

week_of_year()

Extract the week of the year component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).week_of_year()

┌───┐
│ 1 │
└───┘

year

year()

Extract the year component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.date(2024, 12, 31).year()

┌──────┐
│ 2024 │
└──────┘

TimeValue

TimeValue(arg)

A time of day from 0:00:00 to 23:59:59.999999999.

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

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

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> lower = ibis.date(2024, 12, 30)
>>> upper = ibis.date(2025, 1, 1)
>>> ibis.date(2024, 12, 31).between(lower, upper)

┌──────┐
│ True │
└──────┘

>>> ibis.date(2020, 12, 31).between(lower, upper)

┌───────┐
│ False │
└───────┘

hour

hour()

Extract the hour component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).hour()

┌────┐
│ 23 │
└────┘

microsecond

microsecond()

Extract the microsecond component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2024-12-31 23:59:59.999").microsecond()

┌────────┐
│ 999000 │
└────────┘

millisecond

millisecond()

Extract the millisecond component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp("2024-12-31 23:59:59.999").millisecond()

┌─────┐
│ 999 │
└─────┘

minute

minute()

Extract the minute component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).minute()

┌────┐
│ 59 │
└────┘

second

second()

Extract the second component.

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).second()

┌────┐
│ 59 │
└────┘

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

Name	Type	Description
	StringValue	Formatted version of `arg`

time

time()

Return the time component of the expression.

Returns

Name	Type	Description
	TimeValue	The time component of `self`

Examples

>>> import ibis
>>> ibis.options.interactive = True
>>> ibis.timestamp(2024, 12, 31, 23, 59, 59).time()

┌──────────┐
│ 23:59:59 │
└──────────┘

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

Name	Type	Description
	TimeValue	`self` truncated to `unit`

IntervalValue

IntervalValue(arg)

A time duration, eg 6 day or 2 hours or 457 seconds.

This is the result from operations that compute the difference between two date or timestamp expressions, such as Timestamp.delta().

This can be combined with date or timestamp expressions using addition and subtraction, eg ibis.timestamp("2020-01-01") + ibis.interval(days=1), which results in a new timestamp expression.

Attributes

Name	Description
years	The number of years (IntegerValue).
quarters	The number of quarters (IntegerValue).
months	The number of months (IntegerValue).
weeks	The number of weeks (IntegerValue).
days	The number of days (IntegerValue).
hours	The number of hours (IntegerValue).
minutes	The number of minutes (IntegerValue).
seconds	The number of seconds (IntegerValue).
milliseconds	The number of milliseconds (IntegerValue).
microseconds	The number of microseconds (IntegerValue).
nanoseconds	The number of nanoseconds (IntegerValue).

Methods

Name	Description
to_unit	::: {.callout-warning}
negate	Negate an interval expression.

to_unit

to_unit(target_unit, /)

DEPRECATED: IntervalValue.to_unit is deprecated as of v10.0; use as_unit() instead

negate

negate()

Negate an interval expression.

Returns

Name	Type	Description
	IntervalValue	A negated interval value expression

Examples

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

Negate a positive interval of one day to subtract a day from a specific date.

>>> ibis.date(2024, 11, 1) + ibis.interval(days=1).negate()

┌────────────┐
│ 2024-10-31 │
└────────────┘

Negate a negative interval of one day to add a day to a specific date.

>>> ibis.date(2024, 11, 1) + ibis.interval(days=-1).negate()

┌────────────┐
│ 2024-11-02 │
└────────────┘

DayOfWeek

DayOfWeek(expr)

A namespace of methods for days of the week, eg ‘Monday’ or ‘Tuesday’.

Methods

Name	Description
full_name	Get the name of the day of the week, eg ‘Monday’ or ‘Tuesday’.
index	Get the index of the day of the week.

full_name

full_name()

Get the name of the day of the week, eg ‘Monday’ or ‘Tuesday’.

Returns

Name	Type	Description
	StringValue	The name of the day of the week

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2024, 10, 27),
...             date(2024, 10, 28),
...             date(2024, 10, 29),
...             date(2024, 10, 30),
...             date(2024, 10, 31),
...             date(2024, 11, 1),
...             date(2024, 11, 2),
...         ]
...     },
... )
>>> t.date_col.day_of_week.full_name()

┏━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ DayOfWeekName(date_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ string                  │
├─────────────────────────┤
│ Sunday                  │
│ Monday                  │
│ Tuesday                 │
│ Wednesday               │
│ Thursday                │
│ Friday                  │
│ Saturday                │
└─────────────────────────┘

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

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

Examples

>>> from datetime import date
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "date_col": [
...             date(2024, 10, 27),
...             date(2024, 10, 28),
...             date(2024, 10, 29),
...             date(2024, 10, 30),
...             date(2024, 10, 31),
...             date(2024, 11, 1),
...             date(2024, 11, 2),
...         ]
...     },
... )
>>> t.date_col.day_of_week.index()

┏━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ DayOfWeekIndex(date_col) ┃
┡━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int16                    │
├──────────────────────────┤
│                        6 │
│                        0 │
│                        1 │
│                        2 │
│                        3 │
│                        4 │
│                        5 │
└──────────────────────────┘

now

ibis.now()

Return an expression that will compute the current timestamp.

Returns

Name	Type	Description
	`TimestampScalar`	An expression representing the current timestamp.

today

ibis.today()

Return an expression that will compute the current date.

Returns

Name	Type	Description
	`DateScalar`	An expression representing the current date.

date

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

Construct a date scalar or column.

Parameters

Name	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

Name	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")

┌────────────┐
│ 2023-01-02 │
└────────────┘

Create a date scalar from year, month, and day

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

┌────────────┐
│ 2023-01-02 │
└────────────┘

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

Name	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")

┌──────────┐
│ 01:02:03 │
└──────────┘

Create a time scalar from hour, minute, and second

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

┌──────────┐
│ 01:02:03 │
└──────────┘

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

Name	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")

┌─────────────────────┐
│ 2023-01-02 03:04:05 │
└─────────────────────┘

Create a timestamp scalar from components

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

┌─────────────────────┐
│ 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

Name	Type	Description
	`IntervalScalar`	An interval expression

Examples

>>> from datetime import datetime
>>> import ibis
>>> ibis.options.interactive = True
>>> t = ibis.memtable(
...     {
...         "timestamp_col": [
...             datetime(2020, 10, 5, 8, 0, 0),
...             datetime(2020, 11, 10, 10, 2, 15),
...             datetime(2020, 12, 15, 12, 4, 30),
...         ]
...     },
... )

Add and subtract ten days from a timestamp column.

>>> ten_days = ibis.interval(days=10)
>>> ten_days

┌────────────────────────────────────────────────┐
│ MonthDayNano(months=0, days=10, nanoseconds=0) │
└────────────────────────────────────────────────┘

>>> t.mutate(
...     plus_ten_days=t.timestamp_col + ten_days,
...     minus_ten_days=t.timestamp_col - ten_days,
... )

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ timestamp_col       ┃ plus_ten_days       ┃ minus_ten_days      ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ timestamp           │ timestamp           │
├─────────────────────┼─────────────────────┼─────────────────────┤
│ 2020-10-05 08:00:00 │ 2020-10-15 08:00:00 │ 2020-09-25 08:00:00 │
│ 2020-11-10 10:02:15 │ 2020-11-20 10:02:15 │ 2020-10-31 10:02:15 │
│ 2020-12-15 12:04:30 │ 2020-12-25 12:04:30 │ 2020-12-05 12:04:30 │
└─────────────────────┴─────────────────────┴─────────────────────┘

Intervals provide more granularity with date arithmetic.

>>> t.mutate(
...     added_interval=t.timestamp_col
...     + ibis.interval(weeks=1, days=2, hours=3, minutes=4, seconds=5)
... )

┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━┓
┃ timestamp_col       ┃ added_interval      ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━┩
│ timestamp           │ timestamp           │
├─────────────────────┼─────────────────────┤
│ 2020-10-05 08:00:00 │ 2020-10-14 11:04:05 │
│ 2020-11-10 10:02:15 │ 2020-11-19 13:06:20 │
│ 2020-12-15 12:04:30 │ 2020-12-24 15:08:35 │
└─────────────────────┴─────────────────────┘