Skip down to the research record

Compare close dates: 3 rules for date and time fields

Quota Forecast Editorial Team · Published · 9 min read

Dates and timestamps compare with each other, but times and intervals compare only with their own data type, according to PostgreSQL. Before you read a close-date gap, check the data type on each side of the comparison.

Key takeaways

  • check the data type on both sides before you subtract one close date from another, and note that type beside each value in the record.
  • compare dates with timestamps, but keep times and intervals in comparisons with their own type.
  • flag any year calculation that crosses from BC into AD for a manual check.
  • keep proration items out of simple date gaps until you have checked the operation that created them.

Why a close-date comparison can mislead

A moved close date tells you something only when the comparison behind it is valid. A common shortcut is to subtract the old close date from the new one and report the result as slippage. That result is easiest to trust when both sides are dates, or both are timestamps, stored in a form that matches. If one side holds a time of day or a duration, the subtraction needs a second look before anyone reports it.

Before a move goes into a report, decide which pair types the report mixes. A report that combines date pairs, timestamp pairs and time-of-day pairs in one average is harder to check than a report that separates them. Separate groups make it easier to see which rule applied to each row.

A mismatch between types often hides in plain sight. A spreadsheet cell formatted as a date can still hold a time value that came from another export, so the display alone does not settle the type. Read the stored values, not only the formatting, before you trust a gap.

Each rule below names one check you can apply to a pair of values before you report the gap. Vendor pages describe their own products, so the Odoo and Stripe examples work as examples of logic rather than as rules for your pipeline.

Rule 1: dates and timestamps compare with each other

Before you compare anything, you should be familiar with the background information on date/time data types from Section 8.5, according to PostgreSQL.

Time zones add a detail you should record. According to PostgreSQL, the time zone name can be specified in any of the ways described in Section 8.5.3. In these expressions, according to PostgreSQL, the desired time zone can be specified either as a text value, for example 'America/Los_Angeles', or as an interval, for example INTERVAL '-08:00'.

Records you compare should use one time zone form, and the form used on each value should be noted. A value shown in one form in a report may have been stored in another, so the note should say which form the stored value used. For how exports present these values, see date and time formats for forecast exports.

When both sides of a comparison are dates or timestamps, the gap between them is the measurement to report. The report should label the unit it used for that difference, so a reader does not mistake a day count for a time count. When one side holds a time of day, move that pair to the next rule before you report anything.

Keep each pair's identifiers in the same row as its stored values, so every gap traces back to the two values that produced it.

Rule 2: times and intervals compare only with the same type

A value that holds a time of day, or a span of time, belongs in a comparison with its own kind.

In practice, this rule matters when a time-of-day value sits next to a close date in an export. Check which columns hold which type before you build any formula across them. A scan of a few sample rows can show the mix. Separating time-of-day and duration values into their own columns before you compare close dates is a reasonable editorial habit. The type check comes first because a formula will run on any two columns that a spreadsheet allows.

Time bucketing needs its own check. According to PostgreSQL, in the case of full units such as 1 minute or 1 hour, date_bin gives the same result as the analogous date_trunc call, but date_bin can truncate to an arbitrary interval. The documentation distinguishes the two cases, so the bucket type belongs in the notes.

A note of the function name beside each bucket lets a reader see whether a bucket came from a full-unit truncation or from an arbitrary interval. Two reports on the same deals can then be read on the same basis, even when they were built at different times.

Treat any time-of-day column as its own field in the export, and label it in the column header, so a reader does not mistake it for a close date.

For the mistakes that show up when records disagree, see the close date mismatches page.

Rule 3: year arithmetic across BC and AD needs care

A gap that runs across the BC and AD boundary is the case to review first. A useful habit is to pull out any pair where the old and new values fall on opposite sides of that boundary, and mark those rows for a manual check before reporting them. A second reader helps with these rows: someone who did not build the calculation can recompute a few flagged pairs by hand.

Record how each calculation handled the boundary, including which side of it each year was counted from. The check described here is an editorial recommendation from this guide, not a separate requirement from the documentation. Each flagged row can carry the two dates it compared, so the reviewer can recompute the gap without opening the original calculation.

Keeping the boundary check beside the type checks means one review pass covers every pair. A reader who sees a flagged row can then apply the same type and time zone notes that the other pairs received, rather than treating the boundary case as a separate exercise.

The documentation names the issue directly. According to PostgreSQL, there is no 0 AD, so subtracting BC years from AD years should be done with care.

Write the reviewer's recomputed gap next to the result the original calculation produced, so any difference between the two shows on the same row.

Illustrative example: one pair checked before reporting

This example is illustrative only. One row holds a close date of day 10 and a moved close date of day 35, both stored as dates with no time zone. The pair compares two dates, so Rule 1 applies, and the report labels the gap as 25 days. If the same row also held a time of day in another column, that value would move to Rule 2 and stay out of this gap. The reviewer records both stored values beside the result.

Exceptions: items classed as prorations

A proration needs its own question. The Stripe prorations page explains how prorations work with subscriptions and how to manage prorations for your customers, according to Stripe. The same page covers the classification point: Stripe bases this classification on the operation that creates the item, not only on the length of the service period, according to Stripe.

A service length alone should not decide how a proration reads in your records. A note of the operation that created each proration item belongs beside its date. When a proration item appears in a close-date review, decide whether the report counts it as a move at all before any gap is reported.

The Stripe page is a vendor's description of its own subscription billing, so it describes that system rather than a close date in a sales record. It is useful for the logic of classification, not for setting the rules of your forecast.

A monthly schedule is a separate case. According to Odoo, the same computation is done for each month until October. That example describes one product's schedule, and it does not describe your forecast cycle.

When a proration item and a close-date move sit on the same deal, keep them in separate rows, so a billing adjustment is not read as a change in the forecast date.

PublisherSource pageQuoted point
PostgreSQLDate/Time Functions and OperatorsDates and timestamps (with or without time zone) are all comparable, while times (with or without time zone) and intervals can only be compared to other values of the same data type.
OdooDeferred revenues pageThe same computation is done for each month until October.
StripeProrations pageStripe bases this classification on the operation that creates the item, not only on the length of the service period.

Records to keep for each close-date comparison

Keep these notes in the same row as each move, so the pair, its form and its exceptions are read together. Each comparison should carry these notes:

  • the data type of each date value, so the pair is compared within its type.
  • the time zone form used on each stored value.
  • the PostgreSQL version used for any older result, because according to PostgreSQL, versions before 9.6 returned zero for all cases of infinite input.
  • the operation that created each proration item, kept beside its date.
  • the function name used for any time bucket, so the bucket can be reproduced.

When a later reviewer opens the row, the notes show which rule applied to the pair. The reviewer does not need to rebuild the calculation to see whether the comparison was valid. A move's gap, its pair types, its time zone form and any proration note can sit together in one row. For a record of why a date moved, see the close date change log.

Next step: open your close-date change log and confirm each side of a pair is a date or a timestamp, with or without time zone, or is a time or interval kept apart from them, before you compare or report the move.

FAQ: can I compare a date with a time?

Can a date be compared with a time?

No. According to PostgreSQL, times can only be compared to values of the same data type, so a time-of-day value needs its own comparison.

Are timestamps with time zone comparable with dates?

Yes. According to PostgreSQL, dates and timestamps with or without time zone are comparable. Record the time zone form on each stored value, as described in the Rule 1 section above.

Why does BC and AD subtraction need care?

Subtracting BC years from AD years needs care because, according to PostgreSQL, there is no 0 AD. Flag such rows for a manual check and have a second reader recompute a sample.

Does Stripe classify a proration by service length alone?

No. According to Stripe, it bases the classification on the operation that creates the item, not only on the length of the service period. Note the creating operation beside each proration date.

Sources