Function: bucketExpr()

function bucketExpr(
   columnSql: Sql, 
   bucket: "year" | "month" | "day" | "week" | "quarter", 
   timezone: string, 
   fieldType: "date" | "datetime"
): Sql;

Defined in: server/insights/compiler/bucket-sql.ts:39

Truncate a temporal column to a bucket, producing a timestamp (never a timestamptz) so the result is independent of the session TimeZone. Both the bucket keyword and the timezone are bind params; the column is a pre-quoted registry identifier fragment.

The conversion DEPENDS ON THE FIELD'S SEMANTIC TYPE, because the two temporal types need opposite treatment:

• datetime (a PG timestamptz) — <col> AT TIME ZONE <tz> converts the instant to the report timezone's wall clock and yields a timestamp, so date_trunc truncates in the report timezone. Correct.
• date (a PG date) — a date has NO instant and no zone; it is already a wall-clock value. <date> AT TIME ZONE <tz> widens the date to midnight timestamp and then INTERPRETS that midnight in tz, producing a timestamptz. date_trunc(<bucket>, <timestamptz>) then truncates in the SESSION TimeZone (not the report's) and returns a timestamptz, so the bucket label silently depends on server session state and can land on the wrong day/week/month. A date therefore gets a plain widening cast and no timezone conversion at all.

ParametersCopy Anchor Link

ReturnsCopy Anchor Link

Sql