Date data type in search indexes - Tablestore - Alibaba Cloud Documentation Center

You can map data of the Integer or String type in a data table to data of the Date type in various formats in a search index that is created for the data table. When you use the search index to perform a range query, the Date type delivers higher performance than the String type.

Precision and range

The maximum precision of Date data is nanoseconds. The value range of Date data is ["1970-01-01 00:00:00.000000000", "2262-04-11 23:47:16.854775807"].

Date formats

You can map fields of the Integer or String type in a data table to fields of the Date type in a search index that is created for the data table and specify the format of the fields of the Date type. The following table describes the formats.

Field type in data tables

Format of fields of the Date type in search indexes

Integer

You can use a predefined format. The following predefined formats are provided:

"epoch_second": specifies a timestamp in seconds. For example, the "1218197720" timestamp in seconds specifies "2008-08-08 20:15:20".
"epoch_millis": specifies a timestamp in milliseconds. For example, the "1218197720123" timestamp in milliseconds specifies "2008-08-08 20:15:20.123".
"epoch_micros": specifies a timestamp in microseconds. For example, the "1218197720123456" timestamp in microseconds specifies "2008-08-08 20:15:20.123456".
"epoch_nanos": specifies a timestamp in nanoseconds. For example, the "1218197720123456789" timestamp in nanoseconds specifies "2008-08-08 20:15:20.123456789".

String

Custom formats are supported. The following items describe the commonly used Date formats:

yyyy-MM-dd HH:mm:ss.SSS
yyyyMMdd HHmmss
yyyy-MM-dd'T'HH:mm:ss.SSSX

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

For more information, see Custom date formats.

Custom date formats

Character	Description	Example
y	The year.	yyyy: 2008. yy: 08.
M	The month.	M: 7. MM: 07.
d	The day of the month.	d: 8. dd: 08.
a	The time period of a day. In the 12-hour clock system, a day is divided into two periods: ante meridiem (AM) and post meridiem (PM). AM specifies before midday and PM specifies post midday.	a: AM. a: PM.
K	The hour in AM or PM. Valid values: 0 to 11.	K: 0. KK: 00.
H	The hour in a day in the 24-hour clock system. Value values: 0 to 23.	H: 0. HH: 00.
m	The minute.	m: 1. mm: 01.
s	The second.	s: 1. ss: 01.
S	The precision of the second. The precision can contain one to nine digits.	S: 3. SSS: 234. SSSSSSSSS: 123456789.
X	The offset of the time zone.	X: +01;Z. XX: +0130;Z. XXX: +01:30;Z XXXX: +013015;Z. XXXXX: +01:30:15;Z.
x	The offset of the time zone.	x: +01;+00. xx: +0130;+0000. xxx: +01:30;+00:00. xxxx: +013015;+0000. xxxxx: +01:30:15.
'	The custom qualifier.	You can use letters to define custom qualifiers. If you use a string as the qualifier, the string must be enclosed in a pair of single quotation marks (''). Important Spaces and hyphens (-) do not need to be included in single quotation marks.
''	The escape character.	''

Verify a Date format

Before you use a Date format to query data, we recommend that you use TermQuery or DateTimeFormatter in JDK 8 or later to check whether the Date format is valid.

Use TermQuery

Create a search index that includes fields of the specified Date format and use TermQuery to check the Date format. If no error is returned, the Date format is valid.

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);
    // Check 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", "2022-12-12 12:10:03.123")).build())
                  .build());
}

Use DateTimeFormatter

Important

If you use this method to check the Date format in specific time zones, errors may occur.
Make sure that you installed JDK 8 or later.

Call the DateTimeFormatter method in JDK to check the Date format. If no error is returned, the Date format is valid.


import java.time.format.DateTimeFormatter;

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

References

To use data of the Date type, you can configure the Date field when you create a search index in the Tablestore console or by using Tablestore SDKs. For more information, see Create a search index.
Search Index supports various query types for multi-dimensional data queries, including term query, terms query, match all query, match query, phrase match query, range query, prefix query, suffix query, wildcard query, token-based wildcard query, boolean query, geo query, nested query, vector search, and exists query.
If you want to analyze data in a data table, you can use the aggregation feature of the Search operation or execute SQL statements. For example, you can obtain the minimum and maximum values, sum, and total number of rows. For more information, see Aggregation and SQL query.
To quickly export data regardless of the result set order, you can use the Parallel Scan feature.