Datetime functions¶

Postgres / Cockroach¶

Extract¶

class piccolo.query.functions.datetime.Extract(identifier: Date | Time | Timestamp | Timestamptz | QueryString, datetime_component: Literal['century', 'day', 'decade', 'dow', 'doy', 'epoch', 'hour', 'isodow', 'isoyear', 'julian', 'microseconds', 'millennium', 'milliseconds', 'minute', 'month', 'quarter', 'second', 'timezone', 'timezone_hour', 'timezone_minute', 'week', 'year'], alias: str | None = None)¶

Note

This is for Postgres / Cockroach only.

Extract a date or time component from a Date / Time / Timestamp / Timestamptz column. For example, getting the month from a timestamp:

>>> from piccolo.query.functions import Extract
>>> await Concert.select(
...     Extract(Concert.starts, "month", alias="start_month")
... )
[{"start_month": 12}]

Parameters:

identifier – Identifies the column.
datetime_component – The date or time component to extract from the column.

SQLite¶

Strftime¶

Note

This is for SQLite only.

Format a datetime value. For example:

>>> from piccolo.query.functions import Strftime
>>> await Concert.select(
...     Strftime(Concert.starts, "%Y", alias="start_year")
... )
[{"start_month": "2024"}]

Parameters:

identifier – Identifies the column.
datetime_format – A string describing the output format (see SQLite’s documentation for more info).

Database agnostic¶

These convenience functions work consistently across database engines.

They all work very similarly, for example:

>>> from piccolo.query.functions import Year
>>> await Concert.select(
...     Year(Concert.starts, alias="start_year")
... )
[{"start_year": 2024}]

Year¶

piccolo.query.functions.datetime.Year(identifier: Date | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the year as an integer.

Month¶

piccolo.query.functions.datetime.Month(identifier: Date | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the month as an integer.

Day¶

piccolo.query.functions.datetime.Day(identifier: Date | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the day as an integer.

Hour¶

piccolo.query.functions.datetime.Hour(identifier: Time | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the hour as an integer.

Minute¶

piccolo.query.functions.datetime.Minute(identifier: Time | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the minute as an integer.

Second¶

piccolo.query.functions.datetime.Second(identifier: Time | Timestamp | Timestamptz | QueryString, alias: str | None = None) → QueryString¶: Extract the second as an integer.