DateRange

Generates a pair of dates, a start and an end, where the end is a set number of days after the start.

Use it for a row that represents a period rather than a single day, such as a booking, a tenancy, a policy term or an employment record. Two separate Date fields produce two unrelated dates, and the second can be earlier than the first. A DateRange field always produces an end on or after its start.

Sub-fields: start and end. Both must be mapped. See Sub-fields.

Every JSON block on this page is a single field object, one entry in a table's fields array. See Building a Request for the full request structure.

Sub-fields

Sub-field	Example value	Description
`end`	`2024-03-25`	The end of the period, a number of days after `start` set by the interval properties. Never earlier than `start`.
`start`	`2024-03-11`	The start of the period.

Properties

The interval properties set the number of days from the start to the end. The other properties apply to the start, and have the same meaning as on a Date field. See The interval.

Property	Type	Default	Description
`cultureLong`	`string?`	none	A culture name, such as `fr-FR`, whose long date format is used for both dates. `en-US` produces `Friday, March 15, 2024`. Produces text. Cannot be combined with `exactFormat` or `cultureShort`.
`cultureShort`	`string?`	none	A culture name, such as `fr-FR`, whose short date format is used for both dates. `en-US` produces `3/15/2024`. Produces text. Cannot be combined with `exactFormat` or `cultureLong`.
`exactFormat`	`string?`	none	A custom date format string, such as `MMM d, yyyy`, used for both dates. Produces text. Cannot be combined with `cultureShort` or `cultureLong`.
`intervalMax`	`number?`	none	The largest number of days from the start to the end. Required unless `intervalMean` is set.
`intervalMean`	`number?`	none	The center of a normal distribution of interval lengths, in days. Requires `intervalStdDev`. Cannot be combined with `intervalPercentInRange`.
`intervalMin`	`number?`	`0`	The smallest number of days from the start to the end. Must not be negative.
`intervalPercentInRange`	`number?`	none	Percentage (0-100) of rows whose interval is generated from `intervalPercentMin` to `intervalPercentMax`. Cannot be combined with `intervalMean`.
`intervalPercentMax`	`number?`	none	The upper bound of the narrower interval window, in days. Required with `intervalPercentInRange`, and must be within `intervalMin` to `intervalMax`.
`intervalPercentMin`	`number?`	none	The lower bound of the narrower interval window, in days. Required with `intervalPercentInRange`, and must be within `intervalMin` to `intervalMax`.
`intervalStdDev`	`number?`	none	The standard deviation of a normal distribution of interval lengths, in days. Required with `intervalMean`, and must be greater than 0.
`lookupValues`	`object[]`	none	The start dates for `Lookup` mode. Each entry is `{ value, weight }`, where `weight` is relative and defaults to 1.
`map`	`object`	required	Maps `start` and `end` to destination columns. See Sub-fields and Building a Request.
`max`	`string?` (date)	`9999-12-31`	Inclusive upper bound for the whole period, so the end is also on or before it. `Random` and `Clustered` only.
`mean`	`string?` (date)	none	The center of the distribution of starts in `Clustered` mode. Required in that mode, with `stdDev`.
`min`	`string?` (date)	`0001-01-01`	Inclusive lower bound for the start. `Random` and `Clustered` only.
`mode`	`"Sequential"` / `"Random"` / `"Lookup"` / `"Clustered"`	`"Sequential"`	How starts are generated. See Modes.
`nullPercentageMax`	`number`	`0`	Upper bound (0-100) of the percentage of rows that should be null. See `nullPercentageMin`.
`nullPercentageMin`	`number`	`0`	Lower bound (0-100) of the percentage of rows that should be null. The rate is fixed once for the whole field from this range, not recalculated per row. Set equal to `nullPercentageMax` for an exact rate. Has no effect in `Sequential` mode.
`percentInRange`	`number?`	none	Percentage (0-100) of starts generated from `percentMin` to `percentMax` instead of the full range. `Random` only, and rejected in any other mode.
`percentMax`	`string?` (date)	none	Upper bound of the window `percentInRange` applies to. Required with `percentInRange`, and must be within `min` to `max`.
`percentMin`	`string?` (date)	none	Lower bound of the window `percentInRange` applies to. Required with `percentInRange`, and must be within `min` to `max`.
`seed`	`integer`	random	Per-field seed, overriding the request's own `seed` for this field only.
`start`	`string?` (date)	`0001-01-01`	The first row's start. `Sequential` only.
`stdDev`	`number?`	none	The standard deviation of the distribution of starts in `Clustered` mode, in days. Required in that mode, with `mean`.
`stepDays`	`integer?`	`1`	The number of days between the starts of consecutive rows in `Sequential` mode. `0` repeats `start`, and a negative value counts down. Must be a whole number.
`unique`	`boolean`	`false`	Whether every generated pair must differ from the others. See Unique periods.
`weekDays`	`string[]?`	every day	The days of the week the start can be on. The end can be on any day. Rejected in `Lookup` mode.

Modes

The mode sets how each row's start is generated. The interval is generated separately for each row, in every mode.

Sequential increases by a fixed number of days. The first period starts at start, and each later period starts stepDays after the one before. The starts are the same on every run, with or without a seed. The interval is still generated for each row.
Random generates each start independently, with every date from min to max equally likely. percentInRange concentrates a percentage of the starts in a narrower window.
Lookup selects each start from lookupValues, optionally weighted. It never produces a start that is not in the list.
Clustered generates starts in a normal distribution around mean, with stdDev setting its width in days. See Clustered distribution.

Properties by mode

A property that applies only to another mode is ignored, except percentInRange, which is rejected outside Random, and weekDays, which is rejected in Lookup.

Property	Sequential	Random	Lookup	Clustered
`start`, `stepDays`	Yes
`min`, `max`		Yes		Yes
`percentInRange`, `percentMin`, `percentMax`		Yes
`lookupValues`			Yes
`mean`, `stdDev`				Yes
`weekDays`	Yes	Yes		Yes
`exactFormat`, `cultureShort`, `cultureLong`	Yes	Yes	Yes	Yes
`nullPercentageMin`, `nullPercentageMax`		Yes	Yes	Yes
`intervalMin`, `intervalMax`	Yes	Yes	Yes	Yes
`intervalMean`, `intervalStdDev`	Yes	Yes	Yes	Yes
`intervalPercentInRange`, `intervalPercentMin`, `intervalPercentMax`	Yes	Yes	Yes	Yes

Sequential mode never produces nulls. In every other mode, start and end are null on the same rows, so a row never has a start without an end.

In Random and Clustered modes, a start on a day excluded by weekDays is generated again, and a range with no allowed day in it is rejected. weekDays is rejected in Lookup mode, because every start is written exactly as listed.

Each mode checks its own settings before any row is generated, so a missing mean or an empty lookupValues list is rejected immediately.

The interval

The interval is the number of days from the start to the end.

A field with neither intervalMax nor intervalMean is rejected.

With intervalMax and no intervalMean, every whole number of days from intervalMin to intervalMax is equally likely. With intervalMean and intervalStdDev, the lengths follow a normal distribution. A length below intervalMin, or above intervalMax where it is set, is set to that bound, so an end is never earlier than its start.

Intervals are rounded to whole days. The mean and standard deviation can still be fractional. An intervalMean of 14 with an intervalStdDev of 2.5 gives about 95% of periods a length from 9 to 19 days.

Staying inside the bounds

In Random and Clustered modes, the whole period is within min to max, not just the start. A start is never later than max less intervalMin days. When the end of a period would still be after max, a new start is generated until the period fits.

The interval is generated once for each row and kept while a new start is generated, so a period is never shortened to fit. Only its position changes.

An intervalMin longer than the whole range from min to max is rejected before any data is generated.

Sequential and Lookup modes do not use min and max, so their periods can end after max.

Unique periods

With unique set, every row has a different pair of dates. Two periods with the same start and different lengths are different pairs. A request for more rows than the field can produce is rejected before any data is generated, stating how many are available:

Mode	Distinct pairs available
`Random`, and `Clustered` with both `min` and `max`	For each whole-day length from `intervalMin` to `intervalMax`, the number of starts from `min` that leave the end on or before `max`, counting only allowed days when `weekDays` is set.
`Lookup`	The number of different start dates, multiplied by the number of whole-day lengths from `intervalMin` to `intervalMax`.
`Sequential`	One per row, or the number of lengths when `stepDays` is `0`.

There is no fixed limit when intervalMax is not set, when the dates are formatted as text, or in Clustered mode without both min and max. A request in one of these cases that runs out of distinct pairs is rejected during generation.

Examples

A booking of one to two weeks

{
  "type": "DateRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "intervalMin": 7,
  "intervalMax": 14,
  "map": {
    "start": { "column": "checkin", "dataType": "Date" },
    "end": { "column": "checkout", "dataType": "Date" }
  }
}

Sample output:

checkin	checkout
2024-04-17	2024-04-24
2024-08-13	2024-08-24
2024-10-04	2024-10-16
2024-03-20	2024-03-30
2024-02-19	2024-03-01
2024-05-07	2024-05-15

Every checkout is on or before December 31, 2024, because the whole booking is within min to max.

A tenancy of about a year

intervalMean and intervalStdDev give about 95% of terms a length from 275 to 455 days. intervalMin sets the shortest term to 180 days.

{
  "type": "DateRange",
  "mode": "Random",
  "min": "2020-01-01",
  "max": "2023-12-31",
  "intervalMin": 180,
  "intervalMean": 365,
  "intervalStdDev": 45,
  "map": {
    "start": { "column": "term_start", "dataType": "Date" },
    "end": { "column": "term_end", "dataType": "Date" }
  }
}

Sample output:

term_start	term_end
2021-10-27	2022-12-27
2021-08-06	2022-07-17
2021-12-09	2023-03-13
2021-06-17	2022-08-03
2020-05-11	2021-04-29
2020-04-05	2021-04-02

Periods that start on a weekday

weekDays applies to the start only. The end can be on any day.

{
  "type": "DateRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-03-31",
  "weekDays": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
  "intervalMin": 1,
  "intervalMax": 5,
  "map": {
    "start": { "column": "opened", "dataType": "Date" },
    "end": { "column": "closed", "dataType": "Date" }
  }
}

Sample output:

opened	closed
2024-03-25	2024-03-28
2024-02-27	2024-03-01
2024-01-16	2024-01-20
2024-03-20	2024-03-22
2024-02-09	2024-02-12
2024-01-18	2024-01-23

Both dates formatted as text

{
  "type": "DateRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "exactFormat": "dd/MM/yyyy",
  "intervalMin": 30,
  "intervalMax": 90,
  "map": {
    "start": { "column": "from_date", "dataType": "VarChar" },
    "end": { "column": "to_date", "dataType": "VarChar" }
  }
}

Sample output:

from_date	to_date
24/06/2024	26/07/2024
27/09/2024	07/11/2024
13/03/2024	23/05/2024
07/03/2024	19/05/2024
14/02/2024	22/04/2024
10/03/2024	17/04/2024

A formatted pair is text, so both sub-fields must be mapped to text columns such as VarChar.

Weekly reporting periods

Sequential starts at start and moves forward by stepDays. With intervalMin and intervalMax set to the same number, every period has the same length.

{
  "type": "DateRange",
  "mode": "Sequential",
  "start": "2024-01-01",
  "stepDays": 7,
  "intervalMin": 6,
  "intervalMax": 6,
  "map": {
    "start": { "column": "period_start", "dataType": "Date" },
    "end": { "column": "period_end", "dataType": "Date" }
  }
}

Sample output:

period_start	period_end
2024-01-01	2024-01-07
2024-01-08	2024-01-14
2024-01-15	2024-01-21
2024-01-22	2024-01-28
2024-01-29	2024-02-04
2024-02-05	2024-02-11

Start dates from a fixed list

Lookup selects each start from lookupValues. The interval is generated for each row, so two periods with the same start can have different ends.

{
  "type": "DateRange",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-01-01" },
    { "value": "2024-04-01" },
    { "value": "2024-07-01" },
    { "value": "2024-10-01" }
  ],
  "intervalMin": 30,
  "intervalMax": 60,
  "seed": 2,
  "map": {
    "start": { "column": "review_start", "dataType": "Date" },
    "end": { "column": "review_end", "dataType": "Date" }
  }
}

Sample output:

review_start	review_end
2024-10-01	2024-11-05
2024-04-01	2024-05-26
2024-01-01	2024-02-24
2024-04-01	2024-05-13
2024-01-01	2024-02-23
2024-07-01	2024-08-10

Each entry can set a weight relative to the other entries. A weight of 3 against three entries with no weight selects that start for about half of the rows.

Periods clustered around midsummer

Clustered generates most starts near mean, with fewer further away. stdDev is in days, so with a value of 20, about 95% of starts are within 40 days of June 15.

{
  "type": "DateRange",
  "mode": "Clustered",
  "mean": "2024-06-15",
  "stdDev": 20,
  "min": "2024-01-01",
  "max": "2024-12-31",
  "intervalMin": 2,
  "intervalMax": 5,
  "map": {
    "start": { "column": "opened", "dataType": "Date" },
    "end": { "column": "closed", "dataType": "Date" }
  }
}

Sample output:

opened	closed
2024-06-15	2024-06-19
2024-07-09	2024-07-14
2024-06-26	2024-06-28
2024-05-25	2024-05-30
2024-05-24	2024-05-29
2024-07-16	2024-07-20

A start outside min to max is set to the nearer bound, as described in Clustered distribution.

Mostly short stays, with some longer ones

intervalPercentInRange applies to the length rather than the start. It generates a percentage of the lengths from a narrower window, and the rest from the full range from intervalMin to intervalMax.

The rest are generated from the full range, which includes the window, so the proportion of lengths in the window is slightly higher than intervalPercentInRange.

{
  "type": "DateRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "intervalMin": 1,
  "intervalMax": 21,
  "intervalPercentInRange": 80,
  "intervalPercentMin": 2,
  "intervalPercentMax": 3,
  "map": {
    "start": { "column": "checkin", "dataType": "Date" },
    "end": { "column": "checkout", "dataType": "Date" }
  }
}

Sample output:

checkin	checkout
2024-06-09	2024-06-11
2024-12-01	2024-12-04
2024-03-04	2024-03-07
2024-03-31	2024-04-13
2024-11-20	2024-11-22
2024-10-16	2024-10-18

Five of the stays are two or three nights, and one is 13 nights.

Table of Contents

DateRange

Sub-fields

Properties

Modes

Properties by mode

The interval

Staying inside the bounds

Unique periods

Examples

A booking of one to two weeks

A tenancy of about a year

Periods that start on a weekday

Both dates formatted as text

Weekly reporting periods

Start dates from a fixed list

Periods clustered around midsummer

Mostly short stays, with some longer ones