Description

The $dateTrunc expression returns incorrect results for a variety of day, week, month, quarter, and year cases. Affected scenarios include dates before the 2000-01-01 reference date, leap years, timezone boundaries, alternate week origins, ISO year 0000 and BCE dates, and day/week binSize values larger than PostgreSQL interval calendar fields can represent.

The following examples reproduce one failure for each affected unit on ghcr.io/documentdb/documentdb/documentdb-local:pg17-0.114.0:

db.dateTruncExamples.deleteMany({});
db.dateTruncExamples.insertOne({});

db.dateTruncExamples.aggregate([{ $project: { _id: 0, day_2026_12: { $dateTrunc: { date: ISODate("2026-12-15T12:34:56.789Z"), unit: "day", binSize: 1, timezone: "UTC" } } } }]).next();
{ day_2026_12: ISODate('2026-12-11T00:00:00.000Z') }

db.dateTruncExamples.aggregate([{ $project: { _id: 0, week_start_saturday: { $dateTrunc: { date: ISODate("2024-02-07T12:34:56.000Z"), unit: "week", binSize: 1, timezone: "UTC", startOfWeek: "saturday" } } } }]).next();
{ week_start_saturday: ISODate('2024-01-27T00:00:00.000Z') }

db.dateTruncExamples.aggregate([{ $project: { _id: 0, month_ref_before: { $dateTrunc: { date: ISODate("1999-12-31T23:59:59.999Z"), unit: "month", binSize: 2, timezone: "UTC" } } } }]).next();
{ month_ref_before: ISODate('2000-01-01T00:00:00.000Z') }

db.dateTruncExamples.aggregate([{ $project: { _id: 0, quarter_ref_before: { $dateTrunc: { date: ISODate("1999-12-31T23:59:59.999Z"), unit: "quarter", binSize: 2, timezone: "UTC" } } } }]).next();
{ quarter_ref_before: ISODate('2000-01-01T00:00:00.000Z') }

db.dateTruncExamples.aggregate([{ $project: { _id: 0, year_ref_before: { $dateTrunc: { date: ISODate("1999-12-31T23:59:59.999Z"), unit: "year", binSize: 2, timezone: "UTC" } } } }]).next();
{ year_ref_before: ISODate('2000-01-01T00:00:00.000Z') }

The expected results are:

The existing implementation uses PostgreSQL's internal timestamp_age function to calculate a symbolic interval between the input and reference timestamps. Some paths then convert or divide those symbolic calendar components using nominal durations.

Calendar units are not fixed durations: months have different lengths, years may contain leap days, and a local calendar day does not always contain 24 hours. Mixing symbolic intervals with nominal-duration arithmetic therefore causes bin boundaries to drift.

Dates before the reference date also require floor division toward negative infinity. Truncating negative indexes toward zero incorrectly places them in a later bin. The week implementation additionally aligns some startOfWeek values to the wrong reference week.

Finally, the previous implementation performs some timezone adjustment after selecting the bin. This can select the wrong calendar day, month, quarter, or year when an input is near a local boundary relative to UTC.

Solution

The affected units are now calculated in local wall-clock time:

• Convert the input instant to a timezone-free local timestamp before selecting a bin.
• Represent day, week, month, quarter, and year positions as signed unit indexes relative to their applicable 2000-01-01 origin.
• Use one shared, overflow-safe calculation for the lower bin boundary: floor(unitIndex / binSize) * binSize.
• Calculate day and week boundaries directly with checked timestamp arithmetic instead of constructing PostgreSQL intervals, allowing the same algorithm to support both ordinary and large 64-bit bin sizes.
• Align week bins to the first requested startOfWeek on or after 2000-01-01.
• Represent month, quarter, and year positions as signed calendar indexes and construct their boundaries using whole-month offsets.
• Normalize PostgreSQL BCE years into astronomical numbering, where ISO year 0000 represents 1 BCE and year -000001 represents 2 BCE.
• Convert the resulting local boundary back to UTC using the timezone offset in effect at that boundary.
• Preserve the existing out-of-range behavior when a calculated boundary cannot be represented as a PostgreSQL timestamp.

Sub-day units continue to use PostgreSQL date_bin. Month, quarter, and year require calendar indexes because date_bin does not allow strides containing months or larger units.

Validation

Added a regression suite covering:

• Every supported unit
• Default and explicit binSize values
• Multi-unit and large 64-bit day/week bins
• Exact bin boundaries and inputs immediately before and after them
• Dates before the 2000-01-01 reference date
• Negative-bin flooring
• Leap years and historical proleptic-Gregorian dates
• ISO year 0000 and BCE month, quarter, and year truncation
• Fixed-offset and named timezones
• Daylight-saving transitions
• Every startOfWeek value
• BSON Date, Timestamp, and ObjectId inputs
• Literal and field-path expressions

The new cases failed against the previous implementation and pass with these changes.

make -C pg_documentdb check-minimal EXTRA_TESTS=bson_aggregation_date_trunc_tests

Type of Change

• Bug fix
• New feature
• Documentation update
• Refactoring / code cleanup
• DevOps / tooling
• Test improvement
• Other: ___________

AI Disclosure

• No AI tools were used substantially in this PR
• AI tools were used to assist with this PR (please complete below)
  • Tool(s) used: Codex
  • How it was used: Generate SQL test cases, iterative development on pg_documentdb/src/operators/bson_expression_date_operators.c to rewrite buggy functions.

Checklist

• I can explain every change in this PR if asked during review
• I have tested these changes locally
• I have added or updated tests as appropriate
• All commits are signed off (DCO: git commit -s)
• I have read the CONTRIBUTING.md guidelines