Custom datetime formats

You can define a datetime format that is not a standard supported by 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 below.

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

For Data Lake queries date and datetime processing, see Data Lake queries.

This table shows the metadata definitions:

Metadata definition Description 
“epochMillisProperty”:{
	“type”:”integer”,
	“x-dateTimeFormat”:”epoch-millis”
}
Datetime in epoch milliseconds for integer instances.

Example: 1537204639000

 
“americanDateTimeProperty”:{
	“type”:”string”,
	“x-dateTimeFormat”:”M/d/yyyy h:mm:ss[.S] a”
}
American datetime format for string instances.

Examples:

“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.

Example: “6/23/2018”

 
“integerDateProperty”:{
	“type”:”integer”,
	“x-dateTimeFormat”:”yyyyMMdd”
}
8-digit integer date for integer instances.

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.

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"