MySQL查询选择表中时间值等于当前时间的字段 [英] MySQL Query select fields where time value in table is equal to current time

问题描述

我想做的是每分钟运行一个程序.该程序使用 C 代码获取当前时间和日期，然后我尝试获取 MySQL 查询来比较数据库字段中的时间，以查看它是否与当前时间匹配.我只想在从 MySQL 中提取值时使用小时和分钟，而不是秒.我似乎无法让 MySQL 查询工作.

What I want to do is have a program run every minute. The program uses C code to get the current time and date and then I am trying to get a MySQL query to compare the time in the database field to see if it matches the current time. I only want to use the hour and minute when exctraing the value from MySQL and not the seconds. I can not seem to get the MySQL query to work.

注意代码中的%s是让C程序插入c代码生成的当前时间.

Note the %s in the code is for the C program to insert the current time generated by the c code.

这里是 MySQL:

SELECT active, type, date, time, action, command FROM `Alarm` 
WHERE TIME_FORMAT('time', '%H:%i') = '%s'

当我在 snprintf 尝试插入时间变量之后打印查询时，我将其作为输出得到，它似乎试图将值插入 %i 而不是 %s，并且时间的格式是不工作:

WHen I print the query after snprintf trying to insert the time variable I get this as an output it seem that it is trying to insert the value into the %i and not the %s, and that the format for the time is not working:

SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM Alarm WHERE TIME_FORMAT(alarm_time, '%H:6297664') = '< F'

SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM Alarm WHERE TIME_FORMAT(alarm_time, '%H:6297664') = '<�F'

这是 C 代码:

char buffer[1024];
    const char *query = "SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM `Alarm` WHERE (alarm_time='%s')";
//const char *query = "SELECT active, type, date, time, action, command FROM `Alarm` WHERE active = '1'";


//Checking to see if connection to DB is succefful
if (mysql_query(conn, query) != 0)
{
    fprintf(stderr, "%s
", mysql_error(conn));
    exit(1);

} else {

    if (snprintf(buffer, sizeof(buffer), query, current_time) >= sizeof(buffer))
    {
        printf("Issue with Buffer 
");
        exit (-1);

    }

获取时间的其余代码:

char current_time [11];

time_t raw;
time(&raw);

struct tm *time_ptr;
time_ptr = localtime(&raw);

now with the "tm", you can format it to a buffer
char current_date[11];
char current_time [11];     

strftime(current_date, sizeof(current_date), "%m/%d/%Y", time_ptr);
strftime(current_time, sizeof(current_time), "%H:%M", time_ptr);

推荐答案

你不需要 C 代码.你可以在数据库中做任何事情:

You don't need C code for this. You can do everything in the database:

SELECT active, type, date, time, action, command
FROM `Alarm`
WHERE TIME_FORMAT(time, '%H:%i') = TIME_FORMAT(now(), '%H:%i') and
      date = curdate();

但是，我强烈建议您不要采用这种方法.您应该存储最后处理的记录的 ID(或至少是日期/时间).然后你应该选择从那时起的所有 id.

However, I strongly advise you not to take this approach. You should store the id (or at least the date/time) of the last record processed. Then you should select all ids since then.

您建议的方法很可能会在同一分钟内运行两次，或者错过一分钟.

Your suggested method has the significant possibility of running twice in the same minute, or missing a minute.

这篇关于MySQL查询选择表中时间值等于当前时间的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！