The search index feature is able to identify date type data in various formats. You can map strings and integers to dates in the search index. Queries made on dates take less time than queries made on strings in search indexes.
Accuracy and range
The maximum precision of date data is the nanosecond. 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 format of date data that are mapped to strings and integers. The following table describes the data types and formats that are supported
Data type | Supported format |
Integer | You can use a predefined format. The following predefined formats are provided:
|
String | Custom formats are supported. The following list describes some commonly used date formats:
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 the time zone. For more information, see Custom date format. |
Custom date format
Character | Description | Example |
y | Indicates the year. |
|
M | Indicates the month. |
|
d | Indicates the day in the month. |
|
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. |
|
K | Indicates the hour in AM or PM. Valid values: 0 to 11. |
|
H | Indicates the hour in a day in the 24-hour clock system. Value values: 0 to 23. |
|
m | Indicates the minute. |
|
s | Indicates the second. |
|
S | The precision of the second. Valid values: 0 to 999999999. |
|
X | Indicates the offset of the time zone. |
|
x | Indicates 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. |
'' | 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 verify whether the date format is valid.
Use TermQuery
Create a search index that includes the date format to be verified, and use TermQuery to verify 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);
// 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", "2022-12-12 12:10:03.123")).build())
.build());
}Use DateTimeFormatter
Errors may exist if you use this method to verify the date format in some time zones.
Make sure that you have installed JDK 8 or later.
Call the DateTimeFormatter method in JDK to verify 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 search indexes.
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.