Tablestore:Query the date and time data

Prerequisites

• A TableStoreClient is initialized. For more information, see the "Initialize the TableStoreClient" section of the Initialization topic.

• A mapping table is created. For more information, see Create mapping 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.

Parameter

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.

func queryData(client *tablestore.TableStoreClient) {
    // Create an SQL request. 
    request := &tablestore.SQLQueryRequest{Query: "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. 
    response, err := client.SQLQuery(request)
    if err != nil {
        panic(err)
    }

    // Obtain the response result of the SQL statement. 
    resultSet := response.ResultSet
    fmt.Println("response resultset:")
    for resultSet.HasNext() {
        row := resultSet.Next()
        for i := 0; i < len(columns); i++ {
            name := columns[i].Name
            isnull, err := row.IsNull(i)
            if err != nil {
                println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                continue
            }
            if isnull {
                println("[INFO]: column is SQL NULL, name: ", name)
                continue
            }
            switch columns[i].Type {
                case tablestore.ColumnType_DATETIME:
                time, err := row.GetDateTime(i)
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(time.Local().String())
                time, err = row.GetDateTimeByName("datetime_value")
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(time.String())
                case tablestore.ColumnType_TIME:
                duration, err := row.GetTime(i)
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(duration.String())
                duration, err = row.GetTimeByName("time_value")
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(duration.String())
                case tablestore.ColumnType_DATE:
                date, err := row.GetDate(i)
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(date.String())
                date, err = row.GetDateByName("date_value")
                if err != nil {
                    println("[INFO:] get column error, name: ", name, ", error: ", err.Error())
                }
                println(date.String())
            }
        }
    }
}

Sample response:

response resultset:
2023-11-09 10:14:00.01 +0800 CST
2023-11-09 10:14:00.01 +0800 CST
10h14m0.01s
10h14m0.01s
2023-11-09 00:00:00 +0000 UTC
2023-11-09 00:00:00 +0000 UTC