Read date and time data - Tablestore SDK for Java - Tablestore

Use Tablestore SDK for Java to read DATETIME, DATE, and TIME values from SQL query results.

Prerequisites

Install the Tablestore SDK for Java and initialize a client.
Use Tablestore SDK for Java 5.16.0 or later.
Create a mapping table.

Description

Call the sqlQuery method to execute a SELECT statement and use SQL date and time functions to convert numeric values to DATETIME, TIME, or DATE values. The from_unixtime function accepts Unix timestamps in seconds.

SQLQueryResponse sqlQuery(SQLQueryRequest request)

Important

getDateTime returns a value in UTC. To use a different time zone, call withZoneSameInstant to convert the time zone.

The following example converts Unix timestamps in seconds from the event_time column of the example_table mapping table to date and time values and reads the converted values by column name.

String query = "SELECT "
        + "from_unixtime(event_time) AS datetime_value, "
        + "time(from_unixtime(event_time)) AS time_value, "
        + "date(from_unixtime(event_time)) AS date_value "
        + "FROM example_table WHERE tenant_id = 1 LIMIT 1";
SQLQueryRequest request = new SQLQueryRequest(query);

SQLQueryResponse response = client.sqlQuery(request);
SQLResultSet resultSet = response.getSQLResultSet();
while (resultSet.hasNext()) {
    SQLRow row = resultSet.next();
    ZonedDateTime dateTime = row.getDateTime("datetime_value");
    Duration time = row.getTime("time_value");
    LocalDate date = row.getDate("date_value");

    System.out.println(dateTime.withZoneSameInstant(ZoneId.of("Asia/Shanghai")));
    System.out.println(time);
    System.out.println(date);
}

Parameters

SQLQueryRequest contains the following parameter.

Name	Type	Description
query (required)	String	The `SELECT` statement to execute. Use functions such as `from_unixtime`, `time`, and `date` to return the required date and time types.

Response

SQLQueryResponse.rows stores the serialized SQL query result. Call getSQLResultSet() to obtain the parsed SQLResultSet, and then read date and time values from SQLRow.

Methods for reading date and time values

Method	Return type	Description
`getDateTime(int columnIndex)`	ZonedDateTime	Reads a `DATETIME` value by column index.
`getDateTime(String columnName)`	ZonedDateTime	Reads a `DATETIME` value by column name.
`getTime(int columnIndex)`	Duration	Reads a `TIME` value by column index.
`getTime(String columnName)`	Duration	Reads a `TIME` value by column name.
`getDate(int columnIndex)`	LocalDate	Reads a `DATE` value by column index.
`getDate(String columnName)`	LocalDate	Reads a `DATE` value by column name.