>>> 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"})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
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
| 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 |
|---|---|
| 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 | Convert this interval to units of target_unit. |
| negate | Negate an interval expression. |
to_unit
to_unit(target_unit)
Convert this interval to units of target_unit.
negate
negate()
Negate an interval expression.
Returns
| Type | Description |
|---|---|
| IntervalValue | A negated interval value expression |
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. |
today
ibis.today()
Return an expression that will compute the current date.
Returns
| 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 | 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 = TrueCreate 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 = TrueCreate 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 = TrueCreate 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 |