Interval

Generates durations: a length of time rather than a point in time.

Use it for a column that contains how long something took or lasts, such as a call duration, an SLA window, a session length or a warranty period. A value has no date. It is written as hh:mm:ss, with a day count before it where the duration is a day or longer, and fractional seconds where there are any.

For a point in time, use Date, DateTime or Timestamp. For a duration as a number, such as a number of minutes in an Integer column, use Number.

Sub-field: value

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.

Properties

Property	Type	Default	Description
exactFormat	string?	none	A custom duration format string, such as d\.hh\:mm\:ss. Produces text. See Formatting the output.
lookupValues	LookupValue[]	none	The durations for Lookup mode. Required in that mode.
map	object	required	Maps the value sub-field to a destination column. See Building a Request and Formatting the output.
max	string? (duration)	10675199.02:48:05.4775807	Inclusive upper bound. Random and Clustered only.
mean	string? (duration)	none	The center of the distribution in Clustered mode. Required in that mode, with stdDev.
min	string? (duration)	00:00:00	Inclusive lower bound. Random and Clustered only. Must not be greater than max.
mode	"Sequential" / "Random" / "Lookup" / "Clustered"	"Sequential"	How values 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 rows 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? (duration)	none	Upper bound of the window percentInRange applies to. Required with percentInRange, and must be within min to max.
percentMin	string? (duration)	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? (duration)	00:00:00	The first row's value. Sequential only.
stdDev	number?	none	The standard deviation of the distribution in Clustered mode, in seconds. Required in that mode, with mean, and must be greater than 0.
stepDays	number?	see Stepping	Days added to each row in Sequential mode.
stepHours	number?	see Stepping	Hours added to each row in Sequential mode.
stepMinutes	number?	see Stepping	Minutes added to each row in Sequential mode.
stepSeconds	number?	see Stepping	Seconds added to each row in Sequential mode.
unique	boolean	false	Whether every generated value must differ from the others. In Lookup mode, the limit is the number of different durations in lookupValues, and in Sequential mode with a step of 0 it is one. A request over the limit is rejected before any data is generated, stating how many are available. Random and Clustered durations include fractions of a second, so they have no fixed limit.

Interval has no weekDays property, because a duration has no date. It has no cultureShort or cultureLong property, because durations have no culture-specific format.

Writing a duration

start, min, max, mean, percentMin, percentMax and every lookupValues entry are durations written as hh:mm:ss, with an optional day count before it and optional fractional seconds:

• "00:04:00" is four minutes.
• "01:00:00" is one hour.
• "3.00:00:00" is three days.
• "1.06:30:00" is one day and six and a half hours.

A duration of a day or longer must use the d.hh:mm:ss form. The hours must be from 0 to 23, so "36:00:00" is rejected. Write a day and a half as "1.12:00:00".

Modes

• Sequential increases by a fixed step. The first row is start, and each row after it is one combined step longer. The values are not random, so the same request always produces the same values, with or without a seed.
• Random generates each row independently, with every duration from min to max equally likely. percentInRange concentrates a percentage of the rows in a narrower window.
• Lookup selects from the durations in lookupValues, optionally weighted. It never produces a duration that is not in the list. See LookupValue.
• Clustered generates a normal distribution around mean, with stdDev setting its width. See Clustered distribution.

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, stepDays, stepHours, stepMinutes, stepSeconds	Yes
min, max		Yes		Yes
percentInRange, percentMin, percentMax		Yes
lookupValues			Yes
mean, stdDev				Yes
exactFormat	Yes	Yes	Yes	Yes
nullPercentageMin, nullPercentageMax		Yes	Yes	Yes

Sequential mode never produces nulls. Use another mode for a column with null values.

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

Stepping

Sequential mode has four step properties, and their values are added together. stepDays: 1 with stepHours: 6 gives a step of 30 hours. With none of the four set, the step is one second. A step can include fractions of a second.

A combined step of 0 repeats start on every row. A negative step counts down, so a start of zero with a negative step produces negative durations, such as -00:30:00.

LookupValue

Lookup mode selects from a list of durations. Each entry is an object with a value and an optional weight:

Property	Type	Default	Description
value	string (duration)	required	The duration.
weight	number	1	The entry's weight relative to the other entries in the list. Must be greater than 0.

Weights are relative, not percentages, so they do not need to total any particular number. 3 and 1 give the same distribution as 30 and 10: the first entry is selected three times as often as the second. With no weights set, every entry is equally likely.

The list must contain at least one entry.

Response targets from a support plan's three tiers:

{
  "type": "Interval",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "04:00:00", "weight": 5 },
    { "value": "1.00:00:00", "weight": 3 },
    { "value": "00:30:00" }
  ],
  "map": { "value": { "column": "response_target", "dataType": "Interval" } }
}

Sample output:

response_target
04:00:00
00:30:00
04:00:00
1.00:00:00
04:00:00
04:00:00

The weights total 9, so the four-hour target is selected for about five rows in nine. Each row is selected separately, so the proportions are approximate.

Clustered distribution

How mean, stdDev, min and max affect the output. The curve is the number of rows with each value. Most values are near mean, and fewer are further away, at a rate set by stdDev. A value beyond min or max is set to that boundary, shown by the solid bar at each one.

mean sets the center and stdDev sets the width, both in the field's own units. About 68% of values are within one stdDev of mean, about 95% within two, and about 99.7% within three. A larger stdDev spreads the values further from mean, and a smaller one keeps them closer to it.

min and max are optional. Without them, a value can be any distance from mean within the range the field supports. With them, a value outside the range is set to the nearer of min or max. It is not generated again. The solid bars in the diagram are these values.

When the range is narrow relative to stdDev, many rows have a value of exactly min or max. In the diagram, the range is one and a half standard deviations either side of mean. To reduce the number of rows on the boundaries, widen the range or reduce stdDev.

For an Interval field, mean is a duration and stdDev is a number of seconds. With a mean of four minutes and a stdDev of 60, about 68% of rows are from three to five minutes, and about 95% from two to six minutes.

min defaults to zero, so a mean close to zero has many rows at exactly 00:00:00. Set min to a value above zero to avoid this.

Random within a narrower window

percentInRange generates a percentage of the rows of a Random field from a narrower window, and the rest from the full range. percentMin and percentMax set the window, which must be within min to max.

For each row, the field first selects either the window, with a probability of percentInRange, or the full range from min to max. It then generates a duration from the selected range, with every duration equally likely.

The rows generated from the full range can also be in the window, so the proportion of rows in the window is higher than percentInRange. The difference depends on the size of the window relative to the full range. A window that is five percent of the range receives about five percent of the other rows as well. A window that is half the range receives about half of them.

percentInRange does not divide the range into two separate groups. For two separate groups, use two fields, or set min and max so that the whole range is the window.

Use percentInRange for a distribution with a common case and some much longer values, such as call durations that are mostly short with some long calls. Use Clustered instead for values centered on one duration, with fewer further away.

Handling times where most calls are a few minutes long and some are much longer:

{
  "type": "Interval",
  "mode": "Random",
  "min": "00:00:30",
  "max": "02:00:00",
  "percentInRange": 75,
  "percentMin": "00:01:00",
  "percentMax": "00:08:00",
  "seed": 4,
  "map": { "value": { "column": "handle_time", "dataType": "Interval" } }
}

Sample output:

handle_time
01:58:55.4687808
00:03:53.0118238
00:01:22.8799290
00:04:08.5445035
00:06:14.4577163
00:05:07.8724420
00:04:51.2238413
01:03:38.9906292
00:04:19.6446436
01:43:40.6591833
00:06:48.3474008
00:04:00.6036606

Nine of the twelve rows are in the window from one to eight minutes, and three are longer. Each row is selected separately, so a small sample does not match percentInRange exactly.

These requests are rejected before any data is generated:

Request	Error
percentInRange in any mode except Random	percentInRange has no effect on mode 'Clustered'. It only applies to Random.
percentInRange outside 0 to 100	percentInRange must be between 0 and 100, but was 150.
Only one of percentMin and percentMax set	percentInRange requires both percentMin and percentMax to be set.
percentMin below min, or percentMax above max	percentMin (00:00:05) must not be less than min (00:00:30).
percentMin greater than percentMax	percentMin (00:08:00) must not be greater than percentMax (00:01:00).

Formatting the output

Without exactFormat, value is a duration and must be mapped to an Interval column. It is written as hh:mm:ss, with a day count before it for a day or longer, a minus sign for a negative duration, and fractional seconds where there are any.

exactFormat produces text instead, and a field that sets it must be mapped to a text column such as VarChar. The output is the same for every request. In the format string, a literal . or : must be escaped with a backslash, so the format is written d\.hh\:mm\:ss rather than d.hh:mm:ss. In JSON, the backslash is itself escaped, as in the example below. See Custom TimeSpan format strings for the format specifiers.

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.

Increasing by a fixed amount

{
  "type": "Interval",
  "mode": "Sequential",
  "start": "00:15:00",
  "stepMinutes": 15,
  "map": { "value": { "column": "elapsed", "dataType": "Interval" } }
}

Sample output:

elapsed
00:15:00
00:30:00
00:45:00
01:00:00
01:15:00
01:30:00

An SLA window from 1 hour to 3 days

max is longer than a day, so it uses the d.hh:mm:ss form.

{
  "type": "Interval",
  "mode": "Random",
  "min": "01:00:00",
  "max": "3.00:00:00",
  "map": { "value": { "column": "sla_window", "dataType": "Interval" } }
}

Sample output:

sla_window
1.17:50:56.0011550
2.15:42:49.4495392
1.17:22:28.3538904
23:56:21.8880887
1.01:09:52.9632864
2.23:53:06.4974666

Mostly short calls, with some long calls

{
  "type": "Interval",
  "mode": "Random",
  "min": "00:00:30",
  "max": "02:00:00",
  "percentInRange": 85,
  "percentMin": "00:01:00",
  "percentMax": "00:08:00",
  "map": { "value": { "column": "call_duration", "dataType": "Interval" } }
}

Sample output:

call_duration
00:03:51.0036357
00:02:37.0181208
00:06:01.4371342
00:07:51.5960833
00:53:41.6242964
00:02:54.5340975

Call durations clustered around four minutes

min keeps every duration at 10 seconds or more.

{
  "type": "Interval",
  "mode": "Clustered",
  "mean": "00:04:00",
  "stdDev": 60,
  "min": "00:00:10",
  "map": { "value": { "column": "call_duration", "dataType": "Interval" } }
}

Sample output:

call_duration
00:03:58.1030852
00:03:51.9233298
00:03:21.2530881
00:03:39.4298349
00:02:31.7581684
00:02:58.6606022

A custom text format

{
  "type": "Interval",
  "mode": "Random",
  "min": "01:00:00",
  "max": "5.00:00:00",
  "exactFormat": "d\\.hh\\:mm\\:ss",
  "map": { "value": { "column": "lead_time", "dataType": "VarChar" } }
}

Sample output:

lead_time
0.14:22:16
2.15:29:03
3.14:48:02
2.11:33:41
0.22:10:40
4.03:15:00

Null rows

{
  "type": "Interval",
  "mode": "Random",
  "min": "00:05:00",
  "max": "04:00:00",
  "nullPercentageMin": 30,
  "nullPercentageMax": 30,
  "map": { "value": { "column": "time_to_resolve", "dataType": "Interval" } }
}

Sample output:

time_to_resolve
00:32:36.7600160
03:17:45.4838293
01:16:14.9304659
02:20:30.8870124