TimeRange

Generates a pair of times of day, a start and an end, where the end is a set number of minutes after the start.

Use it for a row that represents a period within a day, such as an appointment, a class, a booking or an opening period. Two separate Time fields produce two unrelated times, and the second can be earlier than the first. A TimeRange field always produces an end a set number of minutes after its start.

A time has no date. For a period with dates, use DateTimeRange.

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`	`10:16:11`	The end of the period, a number of minutes after `start` set by the interval properties.
`start`	`09:47:21`	The start of the period.

Properties

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

Property	Type	Default	Description
`cultureLong`	`string?`	none	A culture name whose long time format is used for both values, with seconds. `en-US` produces `1:05:00 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureShort`.
`cultureShort`	`string?`	none	A culture name whose short time format is used for both values, without seconds. `en-US` produces `1:05 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureLong`.
`exactFormat`	`string?`	none	A custom time format string, such as `hh:mm tt`, used for both values. Produces text. Cannot be combined with `cultureShort` or `cultureLong`.
`intervalMax`	`number?`	none	The maximum number of minutes from the start to the end. Required unless `intervalMean` is set.
`intervalMean`	`number?`	none	The center of a normal distribution of the number of minutes. Requires `intervalStdDev`. Cannot be combined with `intervalPercentInRange`.
`intervalMin`	`number?`	`0`	The minimum number of minutes from the start to the end. Must not be negative.
`intervalPercentInRange`	`number?`	none	Percentage (0-100) of rows with a number of minutes from `intervalPercentMin` to `intervalPercentMax`. Cannot be combined with `intervalMean`.
`intervalPercentMax`	`number?`	none	The end of the narrower range of minutes. Required with `intervalPercentInRange`.
`intervalPercentMin`	`number?`	none	The start of the narrower range of minutes. Required with `intervalPercentInRange`.
`intervalStdDev`	`number?`	none	The standard deviation of the number of minutes. Required with `intervalMean`, and must be greater than 0.
`lookupValues`	`LookupValue[]`	none	The start times for `Lookup` mode. Required in that mode.
`map`	`object`	required	Maps `start` and `end` to destination columns. See Building a Request.
`max`	`string?` (time)	`23:59:59`	Inclusive end of the window. The whole period is within it. Earlier than `min` for a window through midnight. `Random` and `Clustered` only.
`mean`	`string?` (time)	none	The center of the distribution of starts in `Clustered` mode. Required in that mode, and must be within `min` to `max`.
`min`	`string?` (time)	`00:00:00`	Inclusive start of the window. `Random` and `Clustered` only.
`mode`	`"Sequential"` / `"Random"` / `"Lookup"` / `"Clustered"`	`"Sequential"`	How the 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. A null row has both values empty. 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 window. `Random` only. See `Time`.
`percentMax`	`string?` (time)	none	End of the window `percentInRange` applies to. Required with `percentInRange`.
`percentMin`	`string?` (time)	none	Start 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?` (time)	`00:00:00`	The first row's start. `Sequential` only.
`stdDev`	`number?`	none	The standard deviation of the starts in `Clustered` mode, in seconds. Required in that mode, and must be greater than 0.
`stepMinutes`	`number?`	none	Minutes between starts in `Sequential` mode, together with `stepSeconds`. See `Time`.
`stepSeconds`	`number?`	none	Seconds between starts in `Sequential` mode, together with `stepMinutes`. See `Time`.
`unique`	`boolean`	`false`	Whether every row must have a different pair of values. See Unique periods.

Modes

The mode sets how each row's start is generated. The interval applies in every mode.

Sequential increases the start by a fixed step. The first row starts at start, and each row after it is one step later. The starts are not random, so the same request always produces the same starts, with or without a seed. The interval is generated for each row.
Random generates each start independently, with every 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.
Clustered generates starts in a normal distribution around mean, with stdDev in seconds setting its width.

Properties by mode

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

Property	Sequential	Random	Lookup	Clustered
`start`, `stepMinutes`, `stepSeconds`	Yes
`min`, `max`		Yes		Yes
`percentInRange`, `percentMin`, `percentMax`		Yes
`lookupValues`			Yes
`mean`, `stdDev`				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. Use another mode for a column with null values.

The interval

The interval is the number of minutes from the start to the end. Fractional minutes are accepted, so 0.5 is thirty seconds. Both values are written to the second.

A field with neither intervalMax nor intervalMean is rejected:

A TimeRange field needs an interval to generate an end from. Set intervalMax for an even spread, or intervalMean and intervalStdDev for a clustered one. Both are measured in minutes.

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 window

In Random and Clustered modes, when min and max are both set, the whole period is within the window, not just the start. A start is never later than max less intervalMin. 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.

The window is measured from min, so a window through midnight works the same way. An intervalMin longer than the window is rejected before any data is generated:

An interval of 61 minutes cannot fit inside min..max, which spans 60 minutes. Widen the bounds, or shorten the interval.

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

Periods past midnight

Without both min and max, an end can be past midnight. It continues from 00:00:00, so a period that starts at 23:30 and lasts an hour ends at 00:30. For a period that ends on the next day as a date, use DateTimeRange.

Unique periods

With unique set, every row has a different pair of values. The interval can be a fraction of a minute, 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 Time 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

Each example below is a single field object, one entry in a table's fields array within a full request, not a complete request on its own. See Building a Request for how it fits into a full DatasetRequest.

Appointments in a clinic day

{
  "type": "TimeRange",
  "mode": "Random",
  "min": "09:00:00",
  "max": "17:00:00",
  "intervalMin": 15,
  "intervalMax": 45,
  "map": {
    "start": { "column": "slot_start", "dataType": "Time" },
    "end": { "column": "slot_end", "dataType": "Time" }
  }
}

Sample output. Every appointment ends by 17:00, because the whole period is within the window:

slot_start	slot_end
09:47:21	10:16:11
14:10:09	14:29:13
12:55:01	13:20:23
12:46:57	13:25:18
15:05:08	15:37:42
11:48:20	12:04:54

Classes of about an hour

{
  "type": "TimeRange",
  "mode": "Random",
  "min": "08:00:00",
  "max": "20:00:00",
  "intervalMin": 30,
  "intervalMean": 60,
  "intervalStdDev": 12,
  "map": {
    "start": { "column": "class_start", "dataType": "Time" },
    "end": { "column": "class_end", "dataType": "Time" }
  }
}

Sample output:

class_start	class_end
16:30:22	17:26:22
16:14:25	17:35:16
09:58:39	11:12:54
18:09:21	19:09:56
16:36:27	18:05:36
17:50:34	19:01:41

A late shift past midnight

max is not set, so the window has no end, and a shift that starts after 22:00 ends after midnight.

{
  "type": "TimeRange",
  "mode": "Random",
  "min": "22:00:00",
  "intervalMin": 120,
  "intervalMax": 240,
  "map": {
    "start": { "column": "on_duty", "dataType": "Time" },
    "end": { "column": "off_duty", "dataType": "Time" }
  }
}

Sample output:

on_duty	off_duty
23:47:44	02:08:18
22:52:16	01:23:20
23:40:10	02:24:41
23:42:28	01:50:37
22:40:21	02:22:59
22:14:46	01:29:29

Both values as formatted text

A formatted range produces text, so map both sub-fields to a text column such as VarChar.

{
  "type": "TimeRange",
  "mode": "Random",
  "min": "09:00:00",
  "max": "17:00:00",
  "exactFormat": "hh\\:mm tt",
  "intervalMin": 30,
  "intervalMax": 60,
  "map": {
    "start": { "column": "opens", "dataType": "VarChar" },
    "end": { "column": "closes", "dataType": "VarChar" }
  }
}

Sample output:

opens	closes
11:59 AM	12:36 PM
11:44 AM	12:41 PM
09:45 AM	10:20 AM
11:06 AM	11:57 AM
10:56 AM	11:47 AM
04:23 PM	04:56 PM

Sequential appointment slots

The slots start every 90 minutes from 09:00 and last an hour, so there are 30 minutes between one slot and the next.

{
  "type": "TimeRange",
  "mode": "Sequential",
  "start": "09:00:00",
  "stepMinutes": 90,
  "intervalMin": 60,
  "intervalMax": 60,
  "map": {
    "start": { "column": "slot_start", "dataType": "Time" },
    "end": { "column": "slot_end", "dataType": "Time" }
  }
}

Sample output:

slot_start	slot_end
09:00:00	10:00:00
10:30:00	11:30:00
12:00:00	13:00:00
13:30:00	14:30:00
15:00:00	16:00:00
16:30:00	17:30:00

Shifts from a fixed set of start times

The weights make each day shift twice as likely as the night shift. Every shift lasts eight hours.

{
  "type": "TimeRange",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "06:00:00", "weight": 2 },
    { "value": "14:00:00", "weight": 2 },
    { "value": "22:00:00" }
  ],
  "intervalMin": 480,
  "intervalMax": 480,
  "map": {
    "start": { "column": "shift_start", "dataType": "Time" },
    "end": { "column": "shift_end", "dataType": "Time" }
  }
}

Sample output:

shift_start	shift_end
22:00:00	06:00:00
14:00:00	22:00:00
06:00:00	14:00:00
22:00:00	06:00:00
14:00:00	22:00:00
06:00:00	14:00:00

Arrivals clustered around lunchtime

stdDev is in seconds, so 2700 is 45 minutes. intervalMin and intervalMax are in minutes.

{
  "type": "TimeRange",
  "mode": "Clustered",
  "mean": "12:30:00",
  "stdDev": 2700,
  "min": "06:00:00",
  "max": "20:00:00",
  "intervalMin": 15,
  "intervalMax": 45,
  "map": {
    "start": { "column": "arrived", "dataType": "Time" },
    "end": { "column": "left", "dataType": "Time" }
  }
}

Sample output:

arrived	left
11:48:07	12:15:17
13:41:41	14:10:39
12:31:31	13:16:07
12:04:55	12:28:31
12:42:11	13:20:22
13:11:47	13:36:12

Table of Contents

TimeRange

Sub-fields

Properties

Modes

Properties by mode

The interval

Staying inside the window

Periods past midnight

Unique periods

Examples

Appointments in a clinic day

Classes of about an hour

A late shift past midnight

Both values as formatted text

Sequential appointment slots

Shifts from a fixed set of start times

Arrivals clustered around lunchtime