The search index feature is able to identify date type data in various formats. You can index dates stored as strings or integers to use in the search index. Queries made on dates take less time than queries made on strings in search indexes.

Accuracy and range

Dates are accurate to nanoseconds. The value range of dates is ["1970-01-01 00:00:00.000000000", "2262-04-11 23:47:16.854775807"].

Date formats

You can specify the data type in which dates are stored when you create search indexes. The following table describes the data types and formats that are supported.

Data type Supported formats
Integer The following predefined formats are provided:
  • "epoch_second": indicates a timestamp in seconds. For example, "1218197720" is converted to "2008-08-08 20:15:20".
  • "epoch_millis": indicates a timestamp in milliseconds. For example, "1218197720123" is converted to "2008-08-08 20:15:20.123".
  • "epoch_micros": indicates a timestamp in microseconds. For example, "1218197720123456" is converted to "2008-08-08 20:15:20.123456".
  • "epoch_nanos": indicates a timestamp in nanoseconds. For example, "1218197720123456789" is converted to "2008-08-08 20:15:20.123456789".
String Custom formats are supported. The following list describes some commonly used date formats:
  • yyyy-MM-dd HH:mm:ss.SSS
  • yyyyMMdd HHmmss
  • yyyy-MM-dd'T'HH:mm:ss.SSSX

In the preceding examples, yyyy indicates the four-digit year, MM indicates the month, dd indicates the day, HH indicates the 24-hour clock, mm indicates the minute, ss indicates the second, SSS indicates the precision of the second, and X indicates the offset of time zone.

For more information, see Custom date format.

Custom date format

Character Description Example
y Indicates the year.
  • yyyy: 2008.
  • yy: 08.
M Indicates the month.
  • M: 7.
  • MM: 07.
d Indicates the day in the month.
  • d: 8.
  • dd: 08.
a Indicates the time period of a day. In the 12-hour clock system, a day is divided into two periods, which are ante meridiem (AM), meaning before midday, and post meridiem (PM), meaning post midday.
  • a: AM.
  • a: PM.
K Indicates the hour in AM or PM. Valid values: 0 to 11.
  • K: 0.
  • KK: 00.
H Indicates the hour in a day in the 24-hour clock system. Value values: 0 to 23.
  • H: 0.
  • HH: 00.
m Indicates the minute.
  • m: 1.
  • mm: 01.
s Indicates the second.
  • s: 1.
  • ss: 01.
S The precision of the second. Valid values: 0 to 999999999.
  • S: 3.
  • SSS: 234.
  • SSSSSSSSS: 123456789.
X Indicates the offset of time zone.
  • X: +01;Z.
  • XX: +0130;Z.
  • XXX: +01:30;Z
  • XXXX: +013015;Z.
  • XXXXX: +01:30:15;Z.
x Indicates the offset of time zone.
  • x: +01;+00.
  • xx: +0130;+0000.
  • xxx: +01:30;+00:00.
  • xxxx: +013015;+0000.
  • xxxxx: +01:30:15.
' Custom string delimiter. Custom strings are made up of uppercase and lowercase characters, and must be enclosed inside a pair of single quotation marks ('').
Notice Spaces and hyphens (-) do not need to be included in single quotation marks.
'' Escape character. ''

Verify a date format

Before you use a date format to query data, we recommend that you verify whether the date format is valid.

  • Create a search index that includes the date format to be verified, and use TermQuery to verify the date format. The following code provides an example.
    public void testDateFormat(SyncClient client, String tableName, String indexName) {
    // Create a search index. 
    CreateSearchIndexRequest request = new CreateSearchIndexRequest();
    request.setTableName(tableName);
    request.setIndexName(indexName);
    IndexSchema indexSchema = new IndexSchema();
    indexSchema.setFieldSchemas(Arrays.asList(
        new FieldSchema("col_date", FieldType.DATE)
        .setIndex(true)
        .setEnableSortAndAgg(true)
        .setDateFormats(Arrays.asList("yyyy-MM-dd HH:mm:ss.SSS"))
    ));
    request.setIndexSchema(indexSchema);
    client.createSearchIndex(request);
    // Verify the date format. If no error is returned, the date format is valid. 
    client.search(SearchRequest.newBuilder()
                  .tableName(tableName)
                  .indexName(indexName)
                  .searchQuery(SearchQuery
                               .newBuilder()
                               .query(QueryBuilders.term("col_date", "2012-12-12 12:10:03.123")).build())
                  .build());
}
  • Use DateTimeFormatter in JDK 8 or later to verify the date format.
    Note There may be errors in the parsed data for some time zones. 
    
import java.time.format.DateTimeFormatter;

public void testFormatByJdk8() {
    DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.SSS").parse("2012-12-12 12:10:03.123");
}