Does the 'time' field support time functions when querying cluster_log?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 查询 cluster_log 时,字段 time 支持时间函数吗?

| username: MrSylar

  1. Query logs for a specified time period
    mysql> SELECT time, instance, left(message, 150) FROM cluster_log WHERE message LIKE ‘%ddl%job%ID.80%’ AND type=‘tidb’ AND time > DATE_SUB(NOW(), INTERVAL 30 SECOND) AND time < now();
    ERROR 1105 (HY000): denied to scan logs, please specify the start time, such as time > '2020-01-01 00:00:00'

  2. It works if using a time string:
    mysql> SELECT time, instance, left(message, 150) FROM cluster_log WHERE message LIKE ‘%ddl%job%ID.80%’ AND type=‘tidb’ AND time > ‘2024-05-20 10:52:14’ AND time < ‘2024-05-20 10:52:44’;
    ±------------------------±---------------±-------------------------------------------------------------------------------------------------------------------------------------------------------+
    | time | instance | left(message, 150) |
    ±------------------------±---------------±-------------------------------------------------------------------------------------------------------------------------------------------------------+
    | 2024/05/20 10:52:15.989 | 127.0.0.1:4000 | [conn.go:1152] [“command dispatched failed”] [conn=4276449321164997027] [connInfo="id:4276449321164997027, addr:127.0.0.1:49908 status:11, collation:u |
    ±------------------------±---------------±-------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.04 sec)

So, does it not support using time functions directly?

| username: zhaokede | Original post link

The select DATE_SUB(NOW(), INTERVAL 300 SECOND) can get the time, but currently unable to connect to the database. It seems that the time has not been parsed and executed.

| username: zhaokede | Original post link

I only have MySQL on hand, and the corresponding time field in the table can be executed.

| username: 像风一样的男子 | Original post link

In the cluster_log table, the time field is of type varchar(32), so comparing it this way is definitely incorrect.

| username: zhaokede | Original post link

Understood. Either compare using strings or forcibly convert to time for comparison.

| username: tidb菜鸟一只 | Original post link

This is the table structure of CLUSTER_LOG, you can treat it as a regular table.

| username: yytest | Original post link

This restriction may be enforced by the database administrator or system settings to control access to log data and reduce reliance on real-time functions, thereby improving performance and security.