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.

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

Here is the MySQL:

SELECT active, type, date, time, action, command FROM `Alarm`

WHERE TIME_FORMAT('time', '%H:%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') = '

Here is the C code:

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\n", mysql_error(conn));

exit(1);

} else {

if (snprintf(buffer, sizeof(buffer), query, current_time) >= sizeof(buffer))

{

printf("Issue with Buffer \n");

exit (-1);

}

Rest of the code that gets the time:

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);

解决方案

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();

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.