MySQL Query select fields where time value in table is equal to current time

Question

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') = '<�F'

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

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. — too honest for this site, Jan 27 '17 at 23:10
If you want to just run a program every minute: this is like using a sledgehammer to crack a nut. Setup a chron job! — too honest for this site, Jan 27 '17 at 23:11
The query runs, but no records are showing up, meaning the where part of the query is not giving me the desired results. — Vlad, Jan 27 '17 at 23:13
True, but I want it to check the DB for the time. That way I can have one cron job that runs, and then based on the DB to deiced to something or not — Vlad, Jan 27 '17 at 23:14
Show us the SQL _after_ sprintf has done the %s substitution. — Rick James, Jan 28 '17 at 23:14
I don't need to see just the value, but the entire SQL statement. — Rick James, Jan 28 '17 at 23:31
@RickJames I updated my question, I hope that is what you are looking for. — Vlad, Jan 28 '17 at 23:36
`query` seems to still have "%s" in it. Can you 'print' query after the time is put into it? — Rick James, Jan 29 '17 at 05:06
@RickJames Here is what outputs: SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM `Alarm` WHERE TIME_FORMAT('time', '%H:6297664') = '<�F' — Vlad, Jan 30 '17 at 19:40
Odd it seems that the c ode is trying to insert the time variable into the %i and not into the %s that it is suppose to — Vlad, Jan 30 '17 at 19:42
`sprintf` will try to use any `%` fields it can. This is a coding bug, not a MySQL problem. — Rick James, Jan 30 '17 at 22:09
@RickJames so is there a way to work around this or fix this issue? — Vlad, Jan 30 '17 at 22:21

score 1 · Answer 1 · answered Jan 27 '17 at 23:10

1

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.

answered Jan 27 '17 at 23:10

Gordon Linoff

1,242,037
58
646
786

IDK that was not me – Vlad Jan 27 '17 at 23:14
How would I go about "You should store the id (or at least the date/time) of the last record processed. Then you should select all ids since then" – Vlad Jan 27 '17 at 23:15
@Vlad . . . That is not the question that you asked. This answer is for the question that you asked, with editorial on how that is not a good approach. – Gordon Linoff Jan 27 '17 at 23:17
The SQL statement above will use server time, so results might be different if client and server have different timezones. – Georg Richter Feb 04 '17 at 08:19

score 0 · Answer 2 · edited May 23 '17 at 10:29

TIME_FORMAT('time', '%H:%i')

You put the name of your time column in single-quotes, which are string literal delimiters, not identifier delimiters. So you're trying to format the time from a literal string 'time' not the value of a column named time.

Try this:

TIME_FORMAT(`time`, '%H:%i')

You might want to read When to use single quotes, double quotes, and backticks in MySQL and https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Another consideration. You said you're using C code, and I see you have %s which I assume is a control character for sprintf(). You should inspect the SQL query after you interpolate values into it. Test that query in the MySQL command-line client or in MySQL Workbench.

Or better yet, don't use sprintf(), use parameterized queries. See http://lgallardo.com/en/2011/06/23/sentencias-preparadas-de-mysql-en-c-ejemplo-completo/ for an example of using parameterized queries in C.

Thank you, but for some reason it still does not want to match the where with time field on the db — Vlad, Jan 27 '17 at 23:24

score 0 · Answer 3 · answered Jan 30 '17 at 22:54

0

sprintf ... '%%H:%%i' ... %s

That is, hide the %H and %i from sprintf by saying that those are literal percents, not substitution places. Only the %s is to be substituted in the C code. By the time it gets to MySQL it will be this string:

... TIME_FORMAT(`time`, '%H:%i') = '12:34'

answered Jan 30 '17 at 22:54

Rick James

135,179
13
127
222

Thank you I was about to fix the % issue, but even when I change the entry in the DB to to match the current time, the value %s in the query is correct, but it does not indicate it as a match. – Vlad Jan 30 '17 at 23:32
Show us specific data values and the SQL. – Rick James Jan 31 '17 at 01:45
What data values are you referring to? And the SQL is in the question. I even tried to change the type from time in the DB to just a varchar, and entered into the field lets say 12:34, so the sql would be : query = "SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM `Alarm` WHERE alarm_time = '%s'"; So i would get ride of the time conversion to see if it would work an still nothing – Vlad Jan 31 '17 at 18:58
I did try this and found it to work: const char *query = "SELECT alarm_active, alarm_category, alarm_date, alarm_time, alarm_action, alarm_command FROM `Alarm` WHERE alarm_time = '16:21'"; the time in the DB is 16:21 – Vlad Jan 31 '17 at 21:32

MySQL Query select fields where time value in table is equal to current time

3 Answers3

Linked