Datetime functions¶

Postgres / Cockroach¶

AtTimeZone¶

Note

This is for Postgres / Cockroach only.

Convert the column to the given timezone. See the Postgres docs for more information.

For example:

class Signing(Table):
    starts = Timestamptz()

>>> await Signing.select(
...     AtTimeZone(Signing.starts, 'EST', alias='starts_est'),
...     Signing.starts,
... )
[{
    'starts_est': datetime.datetime(
        2026, 12, 20, 5, 0
    ),
    'starts': datetime.datetime(
        2026, 12, 20, 10, 0, tzinfo=datetime.timezone.utc
    )
}]

Parameters:: timezone – Valid arguments are 'EST', ZoneInfo('EST') and timedelta(hours=5).

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.