DateTimeRange

Generates a pair of date and time values, a start and an end, where the end is a set number of hours after the start.

Use it for a row that represents a span of time, such as a shift, a call, a meeting, a session or an outage. Two separate DateTime fields produce two unrelated values, and the second can be earlier than the first. A DateTimeRange 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-11 17:45:00`	The end of the span, a number of hours after `start` set by the interval properties. Never earlier than `start`.
`start`	`2024-03-11 09:15:00`	The start of the span.

Properties

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

Property	Type	Default	Description
`cultureLong`	`string?`	none	A culture name whose long date and time format is used for both values, with seconds. `en-US` produces `3/15/2024 1:05:00 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureShort`.
`cultureShort`	`string?`	none	A culture name whose short date and time format is used for both values, without seconds. `en-US` produces `3/15/2024 1:05 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureLong`.
`exactFormat`	`string?`	none	A custom date and time format string, used for both values. Produces text. Cannot be combined with `cultureShort` or `cultureLong`.
`intervalMax`	`number?`	none	The largest number of hours from the start to the end. Required unless `intervalMean` is set.
`intervalMean`	`number?`	none	The center of a normal distribution of interval lengths, in hours. Requires `intervalStdDev`. Cannot be combined with `intervalPercentInRange`.
`intervalMin`	`number?`	`0`	The smallest number of hours 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 hours. Required with `intervalPercentInRange`, and must be within `intervalMin` to `intervalMax`.
`intervalPercentMin`	`number?`	none	The lower bound of the narrower interval window, in hours. Required with `intervalPercentInRange`, and must be within `intervalMin` to `intervalMax`.
`intervalStdDev`	`number?`	none	The standard deviation of a normal distribution of interval lengths, in hours. Required with `intervalMean`, and must be greater than 0.
`lookupValues`	`LookupValue[]`	none	The start values for `Lookup` mode.
`map`	`object`	required	Maps `start` and `end` to destination columns. See Sub-fields and Building a Request.
`max`	`string?` (date-time)	`9999-12-31T23:59:59`	Inclusive upper bound for the whole span, so the end is also on or before it. `Random` and `Clustered` only.
`mean`	`string?` (date-time)	none	The center of the distribution of starts in `Clustered` mode. Required in that mode, with `stdDev`.
`min`	`string?` (date-time)	`0001-01-01T00:00:00`	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. See Random within a narrower window.
`percentMax`	`string?` (date-time)	none	Upper bound of the window `percentInRange` applies to. Required with `percentInRange`.
`percentMin`	`string?` (date-time)	none	Lower bound of the window `percentInRange` applies to. Required with `percentInRange`.
`seed`	`integer`	random	Per-field seed, overriding the request's own `seed` for this field only.
`start`	`string?` (date-time)	`0001-01-01T00:00:00`	The first row's start. `Sequential` only.
`stdDev`	`number?`	none	The standard deviation of the distribution of starts in `Clustered` mode, in seconds. Required in that mode, with `mean`.
`stepDays`	`number?`	see Stepping	Days added to each row's start in `Sequential` mode.
`stepHours`	`number?`	see Stepping	Hours added to each row's start in `Sequential` mode.
`stepMinutes`	`number?`	see Stepping	Minutes added to each row's start in `Sequential` mode.
`stepSeconds`	`number?`	see Stepping	Seconds added to each row's start in `Sequential` mode.
`timeMax`	`string?` (time)	`23:59:59.9999999`	Inclusive end of the time-of-day window for the start. Earlier than `timeMin` means the window continues through midnight. `Random` only, and rejected in any other mode. See The separate time of day.
`timeMean`	`string?` (time)	none	The center of the time-of-day distribution for the start. Required with `timeStdDev`. `Random` only, and rejected in any other mode.
`timeMin`	`string?` (time)	`00:00:00`	Inclusive start of the time-of-day window for the start. Later than `timeMax` means the window continues through midnight. `Random` only, and rejected in any other mode. See The separate time of day.
`timePercentInRange`	`number?`	none	Percentage (0-100) of start times generated from `timePercentMin` to `timePercentMax`. Cannot be combined with `timeMean` and `timeStdDev`. `Random` only, and rejected in any other mode.
`timePercentMax`	`string?` (time)	none	Upper bound of the narrower time-of-day window. Required with `timePercentInRange`. `Random` only, and rejected in any other mode.
`timePercentMin`	`string?` (time)	none	Lower bound of the narrower time-of-day window. Required with `timePercentInRange`. `Random` only, and rejected in any other mode.
`timeStdDev`	`number?`	none	The standard deviation of the time-of-day distribution, in seconds. Required with `timeMean`, and must be greater than 0. `Random` only, and rejected in any other mode.
`unique`	`boolean`	`false`	Whether every generated pair must differ from the others. See Unique spans.
`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 step. The first span starts at start, and each later span starts one combined step of stepDays, stepHours, stepMinutes and stepSeconds 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 whole second 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 seconds.

Properties by mode

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

Property	Sequential	Random	Lookup	Clustered
`start`, `stepDays`, `stepHours`, `stepMinutes`, `stepSeconds`	Yes
`min`, `max`		Yes		Yes
`percentInRange`, `percentMin`, `percentMax`		Yes
`lookupValues`			Yes
`mean`, `stdDev`				Yes
`weekDays`	Yes	Yes		Yes
`timeMin`, `timeMax`		Yes
`timeMean`, `timeStdDev`		Yes
`timePercentInRange`, `timePercentMin`, `timePercentMax`		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 hours from the start to the end. Fractional hours are accepted, so 0.5 is thirty minutes and 7.5 is seven and a half hours. Both values are written to the second, so an interval shorter than a second can produce an end equal to its start.

A field with neither intervalMax nor intervalMean is rejected.

With intervalMax and no intervalMean, every length 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.

Staying inside the bounds

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

The interval is generated once for each row and kept while a new start is generated, so a span 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 spans can end after max.

Spans that cross midnight

An end can be on the day after its start, and outside the start's time-of-day window. The time-of-day properties apply to the start only. A night shift that starts between 22:00 and 23:00 and lasts about eight hours ends the next morning:

{
  "type": "DateTimeRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-02-01",
  "timeMin": "22:00:00",
  "timeMax": "23:00:00",
  "intervalMin": 7.5,
  "intervalMax": 8.5,
  "map": {
    "start": { "column": "shift_start", "dataType": "DateTime" },
    "end": { "column": "shift_end", "dataType": "DateTime" }
  }
}

Sample output:

shift_start	shift_end
2024-01-23 22:34:36	2024-01-24 07:02:37
2024-01-24 22:29:16	2024-01-25 06:11:49
2024-01-03 22:42:32	2024-01-04 07:07:56
2024-01-24 22:23:57	2024-01-25 06:41:04
2024-01-25 22:09:04	2024-01-26 05:43:09
2024-01-05 22:30:43	2024-01-06 06:29:59

max is February 1 rather than January 31, so that a shift starting on the evening of January 31 can end the next morning within the range.

Unique spans

With unique set, every row has a different pair of values. The interval is a number of hours with a fractional part, so pairs only have a fixed limit when intervalMin and intervalMax are the same. Every pair then has the same length, and the limit is the number of distinct starts, counted as for a DateTime field with max reduced by the length. A request for more rows than that is rejected before any data is generated, stating how many are available.

When the length varies, there is no fixed limit, and a request that runs out of distinct pairs is rejected during generation.

Examples

A support call of a few minutes

{
  "type": "DateTimeRange",
  "mode": "Random",
  "min": "2024-06-01",
  "max": "2024-06-30",
  "timeMin": "08:00:00",
  "timeMax": "18:00:00",
  "intervalMin": 0.05,
  "intervalMax": 0.75,
  "map": {
    "start": { "column": "call_start", "dataType": "DateTime" },
    "end": { "column": "call_end", "dataType": "DateTime" }
  }
}

Sample output:

call_start	call_end
2024-06-05 11:03:38	2024-06-05 11:43:36
2024-06-27 17:06:50	2024-06-27 17:23:38
2024-06-06 15:49:09	2024-06-06 16:24:30
2024-06-17 11:25:03	2024-06-17 11:34:33
2024-06-28 12:06:02	2024-06-28 12:34:09
2024-06-25 09:59:12	2024-06-25 10:23:07

Every call is from 3 to 45 minutes long.

A working day of about seven and a half hours

With an intervalMean of 7.5 and an intervalStdDev of 1.25, about 95% of days are from 5 to 10 hours long. intervalMin sets the shortest day to 4 hours.

{
  "type": "DateTimeRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-03-31",
  "weekDays": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
  "timeMin": "08:00:00",
  "timeMax": "09:30:00",
  "intervalMin": 4,
  "intervalMean": 7.5,
  "intervalStdDev": 1.25,
  "map": {
    "start": { "column": "clock_in", "dataType": "DateTime" },
    "end": { "column": "clock_out", "dataType": "DateTime" }
  }
}

Sample output:

clock_in	clock_out
2024-03-22 09:11:24	2024-03-22 17:17:24
2024-01-12 09:02:11	2024-01-12 18:29:24
2024-01-17 09:03:38	2024-01-17 15:47:04
2024-02-05 08:05:06	2024-02-05 14:03:47
2024-02-08 09:18:39	2024-02-08 16:41:50
2024-03-08 08:45:03	2024-03-08 16:50:57

Mostly short outages, with some longer ones

intervalPercentInRange 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": "DateTimeRange",
  "mode": "Random",
  "min": "2024-01-01",
  "max": "2024-12-31",
  "intervalMin": 0.1,
  "intervalMax": 48,
  "intervalPercentInRange": 85,
  "intervalPercentMin": 0.1,
  "intervalPercentMax": 2,
  "map": {
    "start": { "column": "outage_start", "dataType": "DateTime" },
    "end": { "column": "outage_end", "dataType": "DateTime" }
  }
}

Sample output:

outage_start	outage_end
2024-10-08 00:15:03	2024-10-08 00:38:56
2024-06-27 03:24:16	2024-06-27 05:04:03
2024-05-05 15:33:53	2024-05-05 16:22:12
2024-06-09 16:15:10	2024-06-09 17:52:16
2024-10-28 08:48:50	2024-10-30 05:30:32
2024-10-01 19:24:23	2024-10-01 21:00:50

Five of the outages are under two hours, and one is about 45 hours.

A job at the same time each day

Sequential starts at start and moves forward by stepHours. A step of 24 hours keeps the same time of day on each date. With intervalMin and intervalMax both set to 7.5, every run is seven and a half hours long.

{
  "type": "DateTimeRange",
  "mode": "Sequential",
  "start": "2024-03-01T08:00:00",
  "stepHours": 24,
  "intervalMin": 7.5,
  "intervalMax": 7.5,
  "map": {
    "start": { "column": "run_start", "dataType": "DateTime" },
    "end": { "column": "run_end", "dataType": "DateTime" }
  }
}

Sample output:

run_start	run_end
2024-03-01 08:00:00	2024-03-01 15:30:00
2024-03-02 08:00:00	2024-03-02 15:30:00
2024-03-03 08:00:00	2024-03-03 15:30:00
2024-03-04 08:00:00	2024-03-04 15:30:00
2024-03-05 08:00:00	2024-03-05 15:30:00
2024-03-06 08:00:00	2024-03-06 15:30:00

Bookings from a fixed set of slots

Lookup selects each start from lookupValues. The interval is from half an hour to an hour and a half, so two bookings with the same start can have different ends.

{
  "type": "DateTimeRange",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-03-01T09:00:00", "weight": 2 },
    { "value": "2024-03-01T13:00:00" },
    { "value": "2024-03-02T09:00:00" }
  ],
  "intervalMin": 0.5,
  "intervalMax": 1.5,
  "map": {
    "start": { "column": "booked_from", "dataType": "DateTime" },
    "end": { "column": "booked_to", "dataType": "DateTime" }
  }
}

Sample output:

booked_from	booked_to
2024-03-02 09:00:00	2024-03-02 09:55:39
2024-03-02 09:00:00	2024-03-02 09:38:31
2024-03-01 09:00:00	2024-03-01 09:53:03
2024-03-01 09:00:00	2024-03-01 09:37:30
2024-03-01 09:00:00	2024-03-01 10:22:42
2024-03-01 13:00:00	2024-03-01 13:43:43

Calls clustered around mid-afternoon

Clustered generates most starts near mean. stdDev is in seconds, so 5400 is an hour and a half.

{
  "type": "DateTimeRange",
  "mode": "Clustered",
  "mean": "2024-03-01T14:00:00",
  "stdDev": 5400,
  "min": "2024-03-01T06:00:00",
  "max": "2024-03-01T22:00:00",
  "intervalMin": 1,
  "intervalMax": 3,
  "map": {
    "start": { "column": "call_start", "dataType": "DateTime" },
    "end": { "column": "call_end", "dataType": "DateTime" }
  }
}

Sample output:

call_start	call_end
2024-03-01 11:12:30	2024-03-01 12:45:16
2024-03-01 12:19:19	2024-03-01 14:04:09
2024-03-01 13:09:28	2024-03-01 15:48:24
2024-03-01 12:33:42	2024-03-01 14:11:08
2024-03-01 15:06:05	2024-03-01 16:55:40
2024-03-01 14:53:58	2024-03-01 16:07:24

stdDev applies to the start and is in seconds. intervalMin and intervalMax set the length and are in hours.

Table of Contents

DateTimeRange

Sub-fields

Properties

Modes

Properties by mode

The interval

Staying inside the bounds

Spans that cross midnight

Unique spans

Examples

A support call of a few minutes

A working day of about seven and a half hours

Mostly short outages, with some longer ones

A job at the same time each day

Bookings from a fixed set of slots

Calls clustered around mid-afternoon