DateTime

Generates date and time values, with no time zone.

Use it for a column that records when something happened, where every value is in the same time zone, such as a created-at column, an audit entry, a sensor reading or an appointment. It has the same modes as Date, with a time of day added. In Random mode, the time of day can also be generated separately from the date, for example to keep every value within office hours across a whole year.

For a value with a UTC offset, such as rows from more than one time zone, use Timestamp. For a date with no time, use Date, and for a time with no date, use Time.

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
`cultureLong`	`string?`	none	A culture name whose long date and time format is used for the value, with seconds. `en-US` produces `3/15/2024 1:05:00 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureShort`. See Formatting the output.
`cultureShort`	`string?`	none	A culture name whose short date and time format is used for the value, without seconds. `en-US` produces `3/15/2024 1:05 PM`. Produces text. Cannot be combined with `exactFormat` or `cultureLong`. See Formatting the output.
`exactFormat`	`string?`	none	A custom date and time format string, such as `MMM d, yyyy h:mm tt`. Produces text. Cannot be combined with `cultureShort` or `cultureLong`. See Formatting the output.
`lookupValues`	`LookupValue[]`	none	The values 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?` (date-time)	`9999-12-31T23:59:59`	Inclusive upper bound. `Random` and `Clustered` only.
`mean`	`string?` (date-time)	none	The center of the distribution in `Clustered` mode. Required in that mode, with `stdDev`.
`min`	`string?` (date-time)	`0001-01-01T00:00:00`	Inclusive lower bound. `Random` and `Clustered` only. Must not be later 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?` (date-time)	none	Upper bound of the window `percentInRange` applies to. Required with `percentInRange`, and must be within `min` to `max`.
`percentMin`	`string?` (date-time)	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-time)	`0001-01-01T00: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`.
`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.
`timeMax`	`string?` (time)	`23:59:59.9999999`	Inclusive end of the time-of-day window. 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. 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. 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 times of day 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 value must differ from the others. See Unique values.
`weekDays`	`string[]?`	every day	The days of the week the date can be on. See Restricting days of the week. Rejected in `Lookup` mode.

Modes

Sequential increases by a fixed step. The first row is start, and each row after it is one combined step later. 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 whole second from min to max equally likely, both included. percentInRange concentrates a percentage of the rows in a narrower window.
Lookup selects from the values in lookupValues, optionally weighted. It never produces a value that is not in the list. See LookupValue.
Clustered generates a normal distribution around mean, with stdDev setting its width. See Clustered distribution.

Every mode generates the date and the time together. In Random mode only, the time of day can be generated separately. See The separate time of day.

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

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.

The combined step must be a whole number of seconds. Fractional values are accepted where the total is whole, so stepHours: 1.5 gives a step of 90 minutes.

A combined step of 0 repeats start on every row. A negative step counts down.

LookupValue

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

Property	Type	Default	Description
`value`	`string` (date-time)	required	The value.
`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.

A batch schedule, with most runs overnight:

{
  "type": "DateTime",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-03-15T02:00:00", "weight": 6 },
    { "value": "2024-03-15T12:00:00" },
    { "value": "2024-03-15T18:00:00" }
  ],
  "map": { "value": { "column": "batch_run_at", "dataType": "DateTime" } }
}

Sample output:

batch_run_at
2024-03-15 02:00:00
2024-03-15 02:00:00
2024-03-15 18:00:00
2024-03-15 02:00:00
2024-03-15 02:00:00
2024-03-15 02:00:00

The weights total 8, so the 02:00 run is selected for about six rows in eight. 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 a DateTime field, mean is a date and time and stdDev is a number of seconds. With a stdDev of 3600, about 68% of rows are within one hour of mean, and about 95% within two hours.

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 value from the selected range, with every second 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.

The separate time of day

In Random mode, setting any of timeMin, timeMax, timeMean, timeStdDev, timePercentInRange, timePercentMin or timePercentMax generates the date and the time of day separately. The date is generated from min to max, and the time of day from the time properties.

These properties are rejected in every other mode, and the error identifies the property. Each of the other modes sets the complete value:

In Lookup mode, a separate time would produce a value that is not in lookupValues.
In Clustered mode, stdDev is in seconds and already sets the time of day.
In Sequential mode, the step sets the time of each row, and a separate time would put rows out of order.

The time of day can be generated in three ways:

timeMin and timeMax alone give every time in the window an equal chance.
timeMean and timeStdDev give a normal distribution around a time of day. A time outside timeMin to timeMax is set to the nearer bound.
timePercentInRange with timePercentMin and timePercentMax generates a percentage of the times from a narrower part of the window.

timeMin can be later in the day than timeMax. The window then continues from timeMin through midnight to timeMax, so 21:00:00 to 03:00:00 is the six hours around midnight. Swapping the two gives the other eighteen hours.

Only the time of day continues through midnight. The date of each row is generated separately, so a row with a time of 02:00 is at 02:00 on its own date.

timeMean, timePercentMin and timePercentMax are measured from the start of the window rather than from midnight, so they also work in a window that continues through midnight. A timeMean of 00:30:00 in a 21:00 to 03:00 window gives times on both sides of midnight.

timeMean and timePercentInRange cannot be combined. timeMean and timeStdDev must be set together, and timeMean must be within timeMin to timeMax.

A random date in 2024, with the time of day centered on 14:00 within office hours:

{
  "type": "DateTime",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timeMean": "14:00:00",
  "timeStdDev": 3600,
  "timeMin": "09:00:00",
  "timeMax": "17:00:00",
  "map": { "value": { "column": "appointment_at", "dataType": "DateTime" } }
}

Sample output:

appointment_at
2024-02-20 15:19:50
2024-06-03 14:22:35
2024-10-25 13:57:43
2024-07-17 13:33:06
2024-07-01 13:41:56
2024-07-27 15:32:21

The dates are spread across the year, and every time is between 09:00 and 17:00.

Restricting days of the week

weekDays limits the days of the week the date can be on. Days are written by name, such as Monday, and matched without regard to case. If set, the list must contain at least one day.

How an excluded day is handled depends on the mode:

Sequential skips excluded days, so every row has a different value. weekDays requires a combined step of at least one minute in this mode.
Random and Clustered generate a new value until it is on an allowed day, so every value is between min and max. A range with no allowed day in it is rejected before any data is generated.
Lookup rejects weekDays, because every value is written exactly as listed.

In Sequential mode, when the combined step is a whole number of weeks, including 0, every row is on the same day of the week as start. That day must be one of the allowed days, or the request is rejected.

Unique values

With unique set, every row has a different value. A request for more rows than the field can produce is rejected before any data is generated, stating how many are available:

Mode	Distinct values available
`Sequential`	One per row, or only one when the combined step is 0.
`Random`	The number of whole seconds from `min` to `max`.
`Clustered`	The same as `Random` when both `min` and `max` are set.
`Lookup`	The number of different values in `lookupValues`.

There is no fixed limit with weekDays, a separate time of day, or a formatting property set, or in Clustered mode without both min and max. A request in one of these cases that runs out of distinct values is rejected during generation.

Formatting the output

Without a formatting property, value is a date and time and must be mapped to a DateTime column. It is written in the request's csvFormat.dateTimeFormat. See CSV formatting.

exactFormat, cultureShort and cultureLong produce text instead, and a field that sets one must be mapped to a text column such as VarChar. Only one of the three can be set.

exactFormat takes a custom date and time format string. The output is the same for every request. See Custom date and time format strings for the format specifiers.
cultureShort and cultureLong take a culture name and write the value in that culture's short or long date and time format. The short format has no seconds, and the long format includes them. See Culture names for how the names are formed. An unknown culture name is rejected.

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.

One reading per hour

{
  "type": "DateTime",
  "mode": "Sequential",
  "start": "2024-01-01T00:00:00",
  "stepHours": 1,
  "map": { "value": { "column": "reading_at", "dataType": "DateTime" } }
}

Sample output:

reading_at
2024-01-01 00:00:00
2024-01-01 01:00:00
2024-01-01 02:00:00
2024-01-01 03:00:00
2024-01-01 04:00:00
2024-01-01 05:00:00

A combined step

stepDays and stepHours are added together, so each row is 30 hours after the one before.

{
  "type": "DateTime",
  "mode": "Sequential",
  "start": "2024-01-01T09:00:00",
  "stepDays": 1,
  "stepHours": 6,
  "map": { "value": { "column": "checkpoint_at", "dataType": "DateTime" } }
}

Sample output:

checkpoint_at
2024-01-01 09:00:00
2024-01-02 15:00:00
2024-01-03 21:00:00
2024-01-05 03:00:00
2024-01-06 09:00:00
2024-01-07 15:00:00

A random value within a quarter

{
  "type": "DateTime",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-03-31T23:59:59",
  "map": { "value": { "column": "created_at", "dataType": "DateTime" } }
}

Sample output:

created_at
2024-03-02 03:29:36
2024-03-01 15:28:39
2024-01-30 18:30:00
2024-02-01 21:44:23
2024-01-19 00:45:12
2024-03-01 12:27:40

Mostly this month, with some older records

About three rows in four are generated from March 2024, and the rest from the whole range, which also includes March 2024.

{
  "type": "DateTime",
  "mode": "Random",
  "min": "2023-01-01T00:00:00",
  "max": "2024-03-31T23:59:59",
  "percentInRange": 75,
  "percentMin": "2024-03-01T00:00:00",
  "percentMax": "2024-03-31T23:59:59",
  "map": { "value": { "column": "last_touched", "dataType": "DateTime" } }
}

Sample output:

last_touched
2024-03-17 10:05:01
2024-03-12 19:32:29
2024-03-30 05:17:01
2024-03-23 08:36:32
2023-06-28 02:03:01
2024-03-19 13:25:59

Business days only

{
  "type": "DateTime",
  "mode": "Sequential",
  "start": "2024-01-01T09:00:00",
  "stepDays": 1,
  "weekDays": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
  "map": { "value": { "column": "shift_start", "dataType": "DateTime" } }
}

Sample output:

shift_start
2024-01-01 09:00:00
2024-01-02 09:00:00
2024-01-03 09:00:00
2024-01-04 09:00:00
2024-01-05 09:00:00
2024-01-08 09:00:00

Saturday January 6 and Sunday January 7 are skipped, and the sequence continues on Monday.

Support tickets clustered around a release

{
  "type": "DateTime",
  "mode": "Clustered",
  "mean": "2024-05-14T18:00:00",
  "stdDev": 7200,
  "min": "2024-05-14T00:00:00",
  "max": "2024-05-15T23:59:59",
  "map": { "value": { "column": "reported_at", "dataType": "DateTime" } }
}

Sample output:

reported_at
2024-05-14 21:03:09
2024-05-14 17:41:49
2024-05-14 14:18:45
2024-05-14 20:05:46
2024-05-14 20:26:09
2024-05-14 19:41:13

A custom text format

{
  "type": "DateTime",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "exactFormat": "MMM d, yyyy h:mm tt",
  "map": { "value": { "column": "printed_at", "dataType": "VarChar" } }
}

Sample output:

printed_at
Jun 18, 2024 6:15 PM
May 16, 2024 12:08 AM
Feb 23, 2024 8:23 AM
Aug 16, 2024 5:26 PM
Apr 10, 2024 12:49 PM
Jul 26, 2024 11:59 AM

Null rows

{
  "type": "DateTime",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "nullPercentageMin": 25,
  "nullPercentageMax": 25,
  "seed": 10,
  "map": { "value": { "column": "completed_at", "dataType": "DateTime" } }
}

Sample output:

completed_at
2024-09-25 23:24:40
2024-06-27 06:23:40

2024-07-15 23:09:28
2024-01-26 14:47:36

Table of Contents

DateTime

Properties

Modes

Properties by mode

Stepping

LookupValue

Clustered distribution

Random within a narrower window

The separate time of day

Restricting days of the week

Unique values

Formatting the output

Examples

One reading per hour

A combined step

A random value within a quarter

Mostly this month, with some older records

Business days only

Support tickets clustered around a release

A custom text format

Null rows