Timestamp

Generates date and time values with a UTC offset.

Use it for rows from more than one time zone, where the offset is part of the data, such as a global audit log, sign-in events or telemetry. Each row has a time zone from timezones, and the offset is the zone's offset at that date and time, including daylight saving time, so the same zone can have a different offset in January and in July.

Every property except timezones describes the local date and time, before the offset is applied. For values with no offset, use DateTime.

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, such as fr-FR, whose long date and time format is used for the value, with seconds and without the offset. 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, such as fr-FR, whose short date and time format is used for the value, without seconds or the offset. 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 yyyy-MM-dd HH:mm:ss zzz. Produces text. Cannot be combined with cultureShort or cultureLong. See Formatting the output.
lookupValues	LookupValue[]	none	The local dates and times 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, and must be within min to max.
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 the local date and time 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, and must be greater than 0.
stepDays	number?	none	Days added on each row in Sequential mode, together with the other step properties. See Steps.
stepHours	number?	none	Hours added on each row in Sequential mode, together with the other step properties.
stepMinutes	number?	none	Minutes added on each row in Sequential mode, together with the other step properties.
stepSeconds	number?	none	Seconds added on each row in Sequential mode, together with the other step properties.
timeMax	string? (time)	23:59:59	The end of the time-of-day window. Random only. See The separate time of day.
timeMean	string? (time)	none	The center of the time-of-day distribution. Requires timeStdDev, and must be within timeMin to timeMax. Random only.
timeMin	string? (time)	00:00:00	The start of the time-of-day window. Later than timeMax for a window through midnight. Random only.
timePercentInRange	number?	none	Percentage (0-100) of times of day generated from timePercentMin to timePercentMax. Cannot be combined with timeMean. Random only.
timePercentMax	string? (time)	none	The end of the narrower time-of-day window. Required with timePercentInRange. Random only.
timePercentMin	string? (time)	none	The start of the narrower time-of-day window. Required with timePercentInRange. Random only.
timeStdDev	number?	none	The standard deviation of the time of day, in seconds. Required with timeMean, and must be greater than 0. Random only.
timezones	string[]?	UTC	Time zone identifiers, such as Europe/London. Each row has one of them. See Time zones and offsets.
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 local date can be on. See Restricting days of the week. Rejected in Lookup mode.

Modes

mode sets how the local date and time are generated. The time zone is selected separately for each row.

• Sequential increases by a fixed step. The first row is start, and each row after it is one step later. The local values are not random, but the time zones are.
• Random generates each row independently, with every whole second from min to max equally likely. percentInRange concentrates a percentage of the rows in a narrower window.
• Lookup selects from the local values in lookupValues, optionally weighted. 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 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
timeMin, timeMax, timeMean, timeStdDev		Yes
timePercentInRange, timePercentMin, timePercentMax		Yes
timezones	Yes	Yes	Yes	Yes
weekDays	Yes	Yes		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.

Time zones and offsets

timezones is a list of time zone identifiers, such as Europe/London or America/New_York. An unknown identifier is rejected before any data is generated. Each row has a time zone selected at random from the list, with every zone equally likely and independent of the mode. The offset is the zone's UTC offset at the row's local date and time, using the zone's daylight saving rules.

Some local times are affected by a daylight saving change:

• When the clocks go back, a local time occurs twice, and the value has the offset of the second occurrence. In New York on November 3, 2024, 01:30:00 is 01:30:00 -05:00.
• When the clocks go forward, a local time does not occur, and the value is moved forward by the length of the gap. In New York on March 10, 2024, 02:30:00 is 03:30:00 -04:00.

The time zone is selected separately for each row, so in Sequential mode the local values are in order and the offsets are not.

Without timezones, every row has an offset of +00:00.

Steps

stepDays, stepHours, stepMinutes and stepSeconds are added together, so stepDays: 1 with stepHours: 6 is a step of 30 hours. With none set, the step is one second. The total must be a whole number of seconds, because a value is written to the second. A step of 0 repeats start on every row, and a negative step counts down.

LookupValue

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

Property	Type	Default	Description
value	string (date-time)	required	The local date and time, before the offset is applied.
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.

Three publication times, in two time zones:

{
  "type": "Timestamp",
  "mode": "Lookup",
  "lookupValues": [
    { "value": "2024-03-15T09:00:00", "weight": 2 },
    { "value": "2024-03-15T13:00:00" },
    { "value": "2024-03-15T17:00:00" }
  ],
  "timezones": ["Europe/London", "America/New_York"],
  "seed": 3,
  "map": { "value": { "column": "published_at", "dataType": "Timestamp" } }
}

Sample output. The local time is from the list and the time zone is selected separately, so the same local time has more than one offset:

published_at
2024-03-15 09:00:00 -04:00
2024-03-15 17:00:00 +00:00
2024-03-15 13:00:00 +00:00
2024-03-15 09:00:00 -04:00
2024-03-15 09:00:00 +00:00
2024-03-15 13:00:00 -04:00

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 Timestamp field, mean is a local date and time, and stdDev is a number of seconds. With a stdDev of 3600, about 95% of rows are within two hours of mean. The time zone does not affect the distribution.

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 local date and the local 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.

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. Only the time of day continues through midnight. 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, so they also work in a window through midnight.

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

The time of day is local, before the offset is applied, so with several timezones every row has a local time in the window in its own time zone. See Local working hours in each time zone.

Restricting days of the week

weekDays limits the days of the week the local 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. The day is the local date's, before the offset is applied.

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 Lookup field has the number of different values in lookupValues, and a Sequential field with a step of 0 has one value. A request for more rows than that is rejected before any data is generated, stating how many are available.

In the other modes, the value is the local date and time with an offset from a randomly selected time zone, so there is no fixed limit. A request that runs out of distinct values is rejected during generation.

Formatting the output

Without a formatting property, value is a timestamp and must be mapped to a Timestamp column. The value is written in the request's csvFormat.timestampFormat. 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. Include zzz for the offset. 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, without the offset. 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 event per second, in US time zones

No step property is set, so each row is one second after the previous one.

{
  "type": "Timestamp",
  "mode": "Sequential",
  "start": "2024-01-01T00:00:00",
  "timezones": ["America/New_York", "America/Chicago", "America/Los_Angeles"],
  "map": { "value": { "column": "event_at", "dataType": "Timestamp" } }
}

Sample output. The local times are in order, and the time zone of each row is selected separately:

event_at
2024-01-01 00:00:00 -05:00
2024-01-01 00:00:01 -06:00
2024-01-01 00:00:02 -06:00
2024-01-01 00:00:03 -05:00
2024-01-01 00:00:04 -08:00
2024-01-01 00:00:05 -05:00

Random values in five time zones

{
  "type": "Timestamp",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timezones": ["America/New_York", "Europe/London", "Europe/Berlin", "Asia/Tokyo", "Australia/Sydney"],
  "map": { "value": { "column": "logged_in_at", "dataType": "Timestamp" } }
}

Sample output:

logged_in_at
2024-09-30 14:22:49 +02:00
2024-05-19 02:07:54 +09:00
2024-05-28 18:06:34 -04:00
2024-02-17 01:04:43 -05:00
2024-04-18 13:43:41 +01:00
2024-12-02 10:09:37 +11:00

Local working hours in each time zone

The date is in 2024, on a weekday, and the local time is from 09:00 to 17:30 in the row's time zone.

{
  "type": "Timestamp",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timeMin": "09:00:00",
  "timeMax": "17:30:00",
  "weekDays": ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"],
  "timezones": ["Europe/London", "America/New_York", "Asia/Tokyo"],
  "map": { "value": { "column": "ticket_opened_at", "dataType": "Timestamp" } }
}

Sample output:

ticket_opened_at
2024-03-18 16:26:53 -04:00
2024-05-24 12:58:05 +01:00
2024-11-12 12:03:45 +09:00
2024-08-14 15:01:26 -04:00
2024-12-19 13:40:38 +09:00
2024-01-01 17:28:04 +09:00

One time zone with daylight saving time

With one time zone, every row is in that zone, and the offset follows its daylight saving rules.

{
  "type": "Timestamp",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timezones": ["Europe/London"],
  "map": { "value": { "column": "recorded_at", "dataType": "Timestamp" } }
}

Sample output. The offset is +00:00 in winter and +01:00 in summer:

recorded_at
2024-10-31 03:29:42 +00:00
2024-07-11 12:34:10 +01:00
2024-04-29 09:40:11 +01:00
2024-02-21 02:43:28 +00:00
2024-06-30 15:55:23 +01:00
2024-03-28 16:48:54 +00:00

Requests clustered around a launch

{
  "type": "Timestamp",
  "mode": "Clustered",
  "mean": "2024-05-14T18:00:00",
  "stdDev": 3600,
  "min": "2024-05-14T12:00:00",
  "max": "2024-05-15T06:00:00",
  "timezones": ["Europe/London"],
  "map": { "value": { "column": "request_at", "dataType": "Timestamp" } }
}

Sample output:

request_at
2024-05-14 16:46:30 +01:00
2024-05-14 19:39:10 +01:00
2024-05-14 16:29:24 +01:00
2024-05-14 18:28:33 +01:00
2024-05-14 17:47:10 +01:00
2024-05-14 19:32:00 +01:00

A custom text format with the offset

{
  "type": "Timestamp",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timezones": ["Europe/London", "Asia/Tokyo"],
  "exactFormat": "yyyy-MM-dd HH:mm:ss zzz",
  "map": { "value": { "column": "audit_at", "dataType": "VarChar" } }
}

Sample output:

audit_at
2024-06-09 21:17:23 +01:00
2024-10-13 02:19:11 +09:00
2024-11-07 05:39:14 +00:00
2024-02-09 14:39:48 +09:00
2024-09-10 08:09:52 +01:00
2024-12-09 16:47:50 +09:00

Null rows

{
  "type": "Timestamp",
  "mode": "Random",
  "min": "2024-01-01T00:00:00",
  "max": "2024-12-31T23:59:59",
  "timezones": ["Europe/London"],
  "nullPercentageMin": 25,
  "nullPercentageMax": 25,
  "seed": 3,
  "map": { "value": { "column": "acknowledged_at", "dataType": "Timestamp" } }
}

Sample output:

acknowledged_at
2024-03-18 02:03:39 +00:00
2024-05-27 13:27:39 +01:00
2024-02-01 20:54:56 +00:00
2024-03-24 16:47:11 +00:00