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:
|
String | Custom formats are supported. The following items describe the commonly used Date formats:
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. |
|
M | The month. |
|
d | The day of the month. |
|
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. |
|
K | The hour in AM or PM. Valid values: 0 to 11. |
|
H | The hour in a day in the 24-hour clock system. Value values: 0 to 23. |
|
m | The minute. |
|
s | The second. |
|
S | The precision of the second. The precision can contain one to nine digits. |
|
X | The offset of the time zone. |
|
x | The offset of the time zone. |
|
' | 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
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.
When you use a search index to query data, you can use the following query methods: term query, terms query, match all query, match query, match phrase query, prefix query, range query, wildcard query, fuzzy query, Boolean query, geo query, nested query, KNN vector query, and exists query. You can select query methods based on your business requirements to query data from multiple dimensions.
You can sort or paginate rows that meet the query conditions by using the sorting and paging features. For more information, see Perform sorting and paging.
You can use the collapse (distinct) feature to collapse the result set based on a specific column. This way, data of the specified type appears only once in the query results. For more information, see Collapse (distinct).
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.
If you want to obtain all rows that meet the query conditions without the need to sort the rows, you can call the ParallelScan and ComputeSplits operations to use the parallel scan feature. For more information, see Parallel scan.