Custom datetime formats

You can define a datetime format that is not a standard supported by the JSON schema draft-06. To do this, you can use the x-dateTimeFormat custom property in your object metadata. This enables the interpretation of data from third-party applications that do not adhere to the ISO 8601 RFC 3339 standard.

Custom format definitions

The tables in this section show the custom datetime formats that are currently supported.

All dates that are included in a data object are assumed to be in UTC. The value that is provided for x-dateTimeFormat is case-sensitive. Therefore, you must include this value in your metadata exactly as it is defined in the tables.

Compass queries support only the date and datetime formats that are displayed in the tables. Any other formats or variations of a custom format are not supported.

This table shows the metadata definitions:

Metadata definition Description 
“epochMillisProperty”:{
	“type”:”integer”,
	“x-dateTimeFormat”:”epoch-millis”
}
datetime in epoch milliseconds for integer instances. For example, 1537204639000. 
“americanDateTimeProperty”:{
	“type”:”string”,
	“x-dateTimeFormat”:”M/d/yyyy h:mm:ss[.S] a”
}
American datetime format for string instances. For example, “10/9/2017 12:42:01 PM” or “10/9/2017 12:42:01.139 PM”. 
“americanDateProperty”:{
	“type”:”string”,
	“x-dateTimeFormat”:”M/d/yyyy”
}
American date format for string instances. For example, “6/23/2018”. 
“integerDateProperty”:{
	“type”:”integer”,
	“x-dateTimeFormat”:”yyyyMMdd”
}
8-digit integer date for integer instances. For example, 20180601. 
“threeCharMonthProperty”:{
	“type”:”string”,
	“x-dateTimeFormat”:”dd-MMM-yyyy HH:mm:ss”
}
Three-character month for string instances, Oracle format. This format always translates to a US locale. For example, “01-SEP-2018 14:08:23”.

This table shows the tokens for datetime formats:

Token Description
M Month, one or two digits: 1-12
MM Month, two digits: 01-12
MMM 3-character month: JAN, FEB, MAR, etc.
D Day of the month, one or two digits: 1-31
dd Day of the month, two digits: 01-31
yyyy Year, four digits: 0001-9999
HH Hours, 24-hour, two digits: 00-23
h Hours, 12-hour, one or two digits: 1-12
mm Minutes, two digits: 00-59
ss Seconds, two digits: 00-59
[S] Optional fractional seconds, up to nine digits: 0-999999999
a AM/PM designator when using 12-hour format: AM, am, PM, or pm