Timestamp

yyyy and MMM, what are all these patterns?

Surprisingly, you may never need to enter an explicit date or time pattern. Most of the time, with a bit of guidance from you, Tabular will be able to automatically parse the time and date text without needing a specific format. This is the recommended method to try first (be lazy, you deserve it!)

And if you find that the timestamps of your data are wrong, you can always override the automatic behaviour with an explicit list of timestamp formats.

Timestamp Details

The Timestamp definition is common to most field data items, since most measured values occur in context to some point in time (or a range of times).

The Tabular plugin supports the full time resolution of AQUARIUS Time-Series, from the start of Year 1 of the Common Era (0001-01-01 00:00) until the end of year 9999 (9999-12-31 23:59:59.9999999), in 100 nanosecond increments. That should cover any timestamp your field data might need to represent.

By default, the Tabular plugin does most of its date & time parsing quite automatically, once you tell it which column(s) to consider.

PropertyName	Required	Description
Formats	N	An array of date time format pattern strings. When empty, the plugin will attempt to parse the timestamp using the configurations's LocaleName property. This is usually the best choice, since it accepts the widest possible set of formats without allowing accidental month/day ambiguity. Any .NET custom date and time format specifier is supported.
Type	N	Defaults to DateTimeOnly. Must be one of: - DateOnly - TimeOnly - DateTimeOnly - DateTimeOffset - DateAndSurvey123Offset See the Merge behaviour section for details.
UtcOffset	N	A UTC offset in one of these forms: - +H - -H - +H:mm - -H:mm When not set, the UTC offset of the location owning the visit will be used.

Accurately parsing timestamps can be a tricky thing, and the Tabular plugin provides extremely fine control over how your date & time values are extracted from your CSV data.

Timestamp properties have a useful short-form syntax too.

Timestamp support an extended short-form syntax which allows you to specify any the relevant components in a single string separated by pipe | characters.

Component	Default	Example
Column info	None. Some column info is required.	Time = '@TheTime' # Use default type, format, and UTC-offset
Type	DateTimeOnly for Time, StartTime, and EndTime properties. TimeOnly for TimeOnly, StartTimeOnly, EndTimeOnly DateOnly for DateOnly, StartDateOnly, EndDateOnly	Time = '@TheTime | DateTimeOffset' # The column contains unambiguous timestamps
Format	Automatic, using the LocaleName	Time = '@TheTime | yyyy-dd-MM HH:mm' # year-day-month? You monster!
Utc offset	The target location's UTC offset.	Time = '@TheTime | UTC+12:00' # Always use New Zealand standard time. The UTC offset needs to start with "UTC", and then can be followed with an hour or hour-and-minute offset.

You can combine multiple components in single string, to keep your configuration as brief as possible.

Location = '@The Location'
Time = '@The Weird Time | DateTimeOffset | HH-yyyy-MM/dd/mm | UTC+05:45' # Hour, year, month, day, minute, Nepal Standard Time

[Reading]
Value = '@The Temperature'
ParameterId = 'TA'
UnitId = 'degC'

Will parse this craziness:

The Location, The Weird Time, The Temperature
LOC1, 12-2020-06/12/35, 20.5
LOC2, 15-1988-02/08/00, -3.5

When would I use multiple [[Times]] column definitions instead of a single Time definition?

Pretty much never.. Not since v20.3 added the convenient TimeOnly and DateOnly properties.

Note: Using multiple [[Times]] sections is only needed for versions of the plugin before v20.3. Newer versions of the plugin support DateOnly and TimeOnly properties to more-easily handle when dates and times are split across multiple columns.

Use a single Time definition when your timestamp contains both date and time information in a single source. Hopefully this is the case for most data, since that means a simpler configuration (simpler is always better!).

But you will need to use multiple [[Times]] sections if your date and time information is split across multiple CSV columns.

If your CSV was shaped like this (notice the introduction of a comma separating the date from the time):

The Location, The Date, The Time, The Temperature
LOC1, 2020-Jun-12, 12:35, 20.5
LOC2, 1988-Feb-8, 15:10, -3.5

Then this TOML configuration would parse it:

Location = '@The Location'

[[Times]]
ColumnHeader = 'The Time'
Type = 'TimeOnly' # Pull the time from this column

[[Times]]
ColumnHeader = 'The Date'
Type = 'DateOnly' # Pull the date from this column

[Reading]
Value = '@The Temperature'
ParameterId = 'TA'
UnitId = 'degC'

This configuration would also work, using the "short form" for the first Time property, and a long form [[Times]] section for the 2nd property.

Location = '@The Location'
Time = '@The Time | TimeOnly' # Just pull the time from this column

[[Times]]
ColumnHeader = 'The Date'
Type = 'DateOnly' # Pull the date from this column

[Reading]
Value = '@The Temperature'
ParameterId = 'TA'
UnitId = 'degC'

v20.3 adds TimeOnly and DateOnly shortcuts for even smaller configs!

The 20.3.x version of the Tabular CSV plugin has added TimeOnly and DateOnly properties to handle this common scenario, and that will make your configuration even smaller. This will work with 20.3-onwards:

Location = '@The Location'
TimeOnly = '@The Time' # Just pull the time from this column
DateOnly = '@The Date' # Just pull the date from this column

[Reading]
Value = '@The Temperature'
ParameterId = 'TA'
UnitId = 'degC'

The same is true for start/end time-ranges. There are StartTimeOnly, StartDateOnly, EndTimeOnly, and EndDateOnly properties to help you quickly parse CSVs with start and end timestamps split into four columns.

What does the default date-time parsing support?

By default, without specifying any properties aside from the source of time data (ie. usually the CSV column holding the date and time text), the plugin will assume that dates and times are in the same UTC offset as the location owning the field data, in the configuration's LocaleName.

The general pattern accepted is "<Date> <Time>", where:

• <Date> is a locale-specific date component.
• <Time> is a locale-specific time component.
• Whitespace separates the <Date> and <Time> components.
• Either component is optional (and would be best paired with a Type of DateOnly or TimeOnly, of course).

This is usually what you want, but you can always override the default behaviour when your data doesn't match the default expectations.

If you use the default behaviour (ie. if no Formats are explicitly specified) and the configuration LocaleName is also at its default value of en-US, then the following date and time text will be parsed by the Tabular plugin:

Text	Parsed ISO 8601 timestamp	Notes
2020-04-01 13:45:56.123	2020-04-01T13:45:56.1230000	This examples shows millisecond accuracy.
Apr/1/2020 1:45 pm	2020-04-01T13:45:00.0000000	This example uses AP/PM designators in the time component.
10/04/2020 18:45	2020-10-04T18:45:00.0000000	This example uses a 24-hour clock.
2020/05/31 3:25:36.7654321 AM	2020-05-31T03:25:36.7654321	This example shows full 100 nanosecond accuracy.
Monday june 22 2020 5:47	2020-06-22T00:00:00.0000000	This example includes a day of the week and a full month name.
1984-October-29	1984-10-29T00:00:00.0000000	There is no time information here, so it defaults to midnight. Using a Type of DateOnly is recommended here.
07:35	xxxx-xx-xxT07:35:00.0000000	There is no date information here, so the current date is used. Assuming the AQTS app server's date as parsed date component will most certaintly be wrong, so using a Type of TimeOnly is required here.
1978/Jul/29 12:00+09:30	1978-07-29T12:00:00.0000000+09:30	This example includes an explicit UTC offset, and should be used with a Type of DateTimeOffset.

As you can see, the default method already handles a wide variety of differences in formats, full month names, abbreviated months names, upper and lowercase names, and separators between date components.

If you set the configuration to a different LocaleName like es-ES for Spanish, then Lunes Junio 22 2020 17:05 would be parsed correctly as 2020-06-22T17:05:00.0000000.

Merge behaviour of different Type values

When your configuration uses more than one timestamp column, the extracted date and time is merged together according to these rules:

Type value	Merge behaviour	Example Format
DateOnly	The newly parsed date replaces the current date. The current time of day and UTC offset are retained.	yyyy-M-d matches all 1-or-2 digit months and day patterns.
TimeOnly	The newly parsed time replaces the current time. The current date and UTC offset are retained.	H:m matches all 1-or-2 digit hour & minute patterns on a 24-hour clock.
DateTimeOnly	The newly parsed date and time replaces the current date and time. The current UTC offset is retained.	yyyy-M-d H:m:s matches a date time.
DateTimeOffset	The newly parsed date, time, and offset are used. None of the current timestamp components are retained.	O matches any ISO 8601 timestamp.
DateAndSurvey123Offset	The newly parsed date replaces the current date. The newly parsed time of day replaces the current UTC offset. The current time of day is retained.	M/d/yyyy h:m:s tt will parse US-style dates and a time of day of '7:00:00 AM' as a UTC offset of -7 hours. See the Survey123 section for details.

Tips about date and time format patterns

Hopefully you won't need to specify any explicit pattern strings, and the default date & time parsing behaviour will just work.

But sometimes data arrives in non-standard formats, and that is when you will need to assign one or more pattern strings to the Formats array. The first pattern to match your data will "win".

Pattern strings in the Formats array are .NET custom date/time format strings.

These format strings can be rather fussy to deal with, so take care to consider some of the common edge cases:

• Format strings are case-sensitive. Common mistakes are made for month-vs-minute and 24-hour-vs-12-hour patterns.
• Uppercase 'M' matches month digits, between 1 and 12.
• Lowercase 'm' matches minute digits, between 0 and 59.
• Uppercase 'H' matches 24-hour hour digits, between 0 and 23.
• Lowercase 'h' matches 12-hour hour digits, between 1 and 12, and require a 't' or 'tt' pattern to distinguish AM from PM.
• Prefer single-character patterns when possible, since they match double-digit values as well. Eg. 'H:m' will match '2:35' and '14:35', but 'HH:mm" will not match '2:35' since the 'HH' means exactly-2-digits.

What is the DateAndSurvey123Offset format?

ESRI's popular Survey123 product will often export its date and time information in a very strange format.

ESRI, if you are listening, this is the most bizarre UTC offset representation we have seen in quite a while.

Survey123 often supplies a text string containing a date plus a non-standard definition of the UTC offset.

We've seen dates from Survey123 come as a combination of a Date plus what looks like a time but is actually an offset from UTC.

Eg. 12/31/2019 7:00:00 AM would represent Dec 31 2019, in UTC-07:00. So that time portion is "the time in UTC when your local timezone is midnight". That is very, very strange.

Since September of 2019, some Survey123 customers (but not all?) have seen 12 hours added to their already-non-standard offset. So the text would become 12/31/2019 7:00:00 PM. Agh! This is madness!

So how can the Tabular plugin cope with these inconsistencies? You have a few choices available:

1. Use the DateOnly instead

If you change the Type from DateAndSurvey123Offset to the simpler DateOnly value, then the weird ESRI time-that-is-really-an-offset-but-not-quite will be ignored, and the location's UTC offset will be used.

This is probably the most robust workaround.

2. Keep using DateAndSurvey123Offset, but set an explicit UtcOffset value

Time = '@TheTime | DateAndSurvey123Offset | UTC-07:00' # Force all Survey123 timestamps into UTC-7