Malloy Documentation
search

Timestamp Operations

VSCode document

Truncation

For truncation to a time unit (timeframe), use the period (.) operator followed by the desired timeframe.

truncation expr = @2021-08-06 00:36:15
expr.minute 2021-08-06 00:36
expr.hour 2021-08-06 00:00
expr.day 2021-08-06 00:00
expr.week 2021-08-01 00:00 (the week containing the 10th)
expr.month 2021-08-01 00:00
expr.quarter 2021-06-01 00:00
expr.year 2021-01-01 00:00

  • See Timezones for information on how truncation interacts with timezones.

Extraction

To extract an integer from a component of a timestamp, malloy uses the time unit (timeframe) in the form of a funtion call.

The "Result" column uses a value of @2021-08-06 00:55:05 for expr.

expression meaning result
day_of_year(expr) day of year, 1-365 218
day(expr) day of month 1-31 5
day_of_week(expr) day of week 1-7 6 (Note: 1 represents Sunday)
week(expr) week in year, 1-53 31
quarter(expr) quarter in year 1-4 3
hour(expr) hour of day 0-23 0
minute(expr) minute of hour 0-59 55
second(expr) second of minute 0-59 5

  • See Timezones for information on how extraction interacts with timezones.

Casting

Timestamps and dates can be cast between types using the :: operator.

Casting to and from Date

When casting between date and timestamp-like types (timestamp or timestamptz), the query timezone is used to determine the conversion:

expression meaning
date_value::timestamp Interprets the date as midnight in the query timezone, returns a timestamp
date_value::timestamptz Interprets the date as midnight in the query timezone, returns a timestamptz
timestamp_value::date Converts the timestamp to a date in the query timezone
timestamptz_value::date Converts the timestamptz to a date in the query timezone

For example, with timezone: 'America/Mexico_City':

  • @2020-02-20::timestamptz creates a timestamptz representing midnight on Feb 20 in Mexico City

  • @2020-02-20 00:00:00[UTC]::date produces @2020-02-19 (because midnight UTC is Feb 19 in Mexico City)

Casting between Timestamp and Timestamptz

expression meaning
timestamp_value::timestamptz Converts timestamp to timestamptz (when no query timezone, interprets as UTC)
timestamptz_value::timestamp Converts timestamptz to timestamp in the query timezone

  • See Timezones for more information on how timezones interact with casting.