How to query the date and time data in the response of an SQL statement - Tablestore

This topic describes how to query the datetime data, date data, and time data in the response of an SQL statement.

Prerequisites

Tablestore SDK for Java V5.16.0 or later is installed. For more information, see Install Tablestore SDK for Java.
Important
Tablestore SDK for Java V5.16.0 and later support the return of the datetime data, date data, and time data in the response of an SQL statement. For more information about the version history of Tablestore SDK for Java, see Version history of Tablestore SDK for Java.
A client is initialized. For more information, see the "Initialize a client" section of the Initialization topic.
A mapping table is created. For more information, see Create mapping tables for tables.

API operations

The following table describes the API operations that you can call to query different types of date and time data. Select an operation based on the type of date and time data that you want to query.

Important

The default time zone of the data returned by the getDateTime operation is in UTC. Convert the time zone based on your business requirements.

Time type	Operation	Parameter	Return value type
Datetime	getDateTime	columnIndex (INT type)	java.time.ZonedDateTime
Datetime	getDateTime	columnName (STRING type)	java.time.ZonedDateTime
Time	getTime	columnIndex (INT type)	java.time.Duration
Time	getTime	columnName (STRING type)	java.time.Duration
Date	getDate	columnIndex (INT type)	java.time.LocalDate
Date	getDate	columnName (STRING type)	java.time.LocalDate

Parameter

Parameter	Description
query	The SQL statement. Configure the parameter based on the required feature.

Example

You can execute the select from_unixtime(time_col) as datetime_value, time(from_unixtime(time_col)) as time_value, or date(from_unixtime(time_col)) as date_value from test_table limit 1 statement to query data in the time_col column of the test_table table and convert the data to the datetime data, time data, and date data. One row of data is returned at most. The system returns the request type, response schema, and response result of the SQL statement.

private static void queryData(SyncClient client) {
  // Create an SQL request. 
  SQLQueryRequest request = new SQLQueryRequest("select from_unixtime(time_col) as datetime_value,time(from_unixtime(time_col)) as time_value, date(from_unixtime(time_col)) as date_value from test_table limit 1");

  // Obtain the response to the SQL request. 
  SQLQueryResponse response = client.sqlQuery(request);

  // Obtain the SQL request type. 
  System.out.println("response type: " + response.getSQLStatementType());

  // Obtain the response schema of the SQL statement. 
  SQLTableMeta tableMeta = response.getSQLResultSet().getSQLTableMeta();
  System.out.println("response table meta: " + tableMeta.getSchema());

  // Obtain the response result of the SQL statement. 
  SQLResultSet resultSet = response.getSQLResultSet();
  System.out.println("response resultset:");
  while (resultSet.hasNext()) {
      SQLRow row = dateTypeSelectResultSet.next();
      System.out.println(row.getDateTime(0).withZoneSameInstant(ZoneId.systemDefault()) + ", " + row.getDateTime("datetime_value").withZoneSameInstant(ZoneId.systemDefault()) + ", " +
              row.getTime(1) + ", " + row.getTime("time_value") + ", " +
              row.getDate(2) + ", " + row.getDate("date_value"));
  }
}

Sample response:

date type select query begin
response type: SQL_SELECT
response table meta: [datetime_value:DATETIME, time_value:TIME, date_value:DATE]
response resultset:
2023-11-09T10:14:00.010+08:00[Asia/Shanghai], 2023-11-09T10:14:00.010+08:00[Asia/Shanghai], PT10H14M0.01S, PT10H14M0.01S, 2023-11-09, 2023-11-09
select query end