python mysql.connector selecting rows with current date in a JOIN clause

Question

I have the following piece of code which joins two tables and prints in a 2D list

print("Resident:\tRoom:\t\tLast check-in:")
print(' ')
join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id"
cursor.execute(join)
output = cursor.fetchall()
for x in range(len(output)):
    for y in range(len(output[x])):
        print(output[x][y], end = '\t | \t')
    print()

giving me the following output:

Resident:   Room:       Last check-in:
 
Name 1   |  W235     |  2020-05-10 17:57:17  |  
Name 2   |  E289     |  2020-05-10 17:58:08  |  
Name 1   |  W235     |  2020-05-10 18:18:22  |  
Name 2   |  E289     |  2020-05-10 18:18:36  |  
Name 1   |  W235     |  2020-05-10 18:18:41  |  
Name 1   |  W235     |  2020-05-16 16:01:38  |  
Name 2   |  E289     |  2020-05-16 16:07:31  |  
Name 2   |  E289     |  2020-05-16 17:13:50  |  
Name 2   |  E289     |  2020-05-16 17:13:53  |  
Name 2   |  E289     |  2020-05-16 17:13:58  |  
Name 1   |  W235     |  2020-06-29 22:41:23  |  
Name 2   |  E289     |  2020-07-15 16:27:13  |  
Name 1   |  W235     |  2020-07-15 16:27:23  |

I need the output to be only those rows which checked-in today (in my output it's the last two rows). I tried manipulating with my "join" line, for example, changing it to:

join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id AND attendance.clock_in LIKE CURRENT_DATE"

but in all my attempts I get empty output instead of what I need:

Resident:   Room:       Last check-in:

So how do I type it correctly so that cursor selects only rows which include today's date?

Are you sure that the `attendance.clock_in LIKE CURRENT_DATE` part belongs in your JOIN? Maybe it would be more suited in a WHERE — Be Chiller Too, Jul 15 '20 at 14:24
@BeChillerToo I actually tried placing it in WHERE but it still gave me empty output — Yarman53, Jul 15 '20 at 14:29
What is `CURRENT_DATE`? Please edit your question to show your full code — Be Chiller Too, Jul 15 '20 at 14:31
@BeChillerToo It's not something I declared in my program, I just found it [online](https://w3resource.com/mysql/date-and-time-functions/mysql-current_date-function.php) and attempted to apply it to my program. — Yarman53, Jul 15 '20 at 14:33
https://stackoverflow.com/questions/55026314/filter-by-date-using-sql — Be Chiller Too, Jul 15 '20 at 14:36
You should search the documentation to know what you are using. CURRENT_DATE is a function, so calling "WHERE attendance.date LIKE CURRENT_DATE" will return the rows where the date is like '2020-07-15', and no rows in your table has this form. You should add a percent sign to show what part of your date is fixed and which part can change. — Be Chiller Too, Jul 15 '20 at 14:38
@BeChillerToo Actually [this](https://stackoverflow.com/questions/14769026/how-to-select-rows-that-have-current-days-timestamp) is the closest to my problem I could find, however there is no JOIN which I have in my code. So now I struggle applying it to JOIN. — Yarman53, Jul 15 '20 at 14:48

score 2 · Accepted Answer · answered Jul 15 '20 at 14:51

2

join = "SELECT users.name, users.room, attendance.clock_in FROM users JOIN attendance ON users.id = attendance.user_id WHERE attendance.clock_in >= CURDATE() AND attendance.clock_in < CURDATE() + INTERVAL 1 DAY"

answered Jul 15 '20 at 14:51

Ahmed ElMetwally

2,276
3
10
15

score 1 · Answer 2 · answered Jul 15 '20 at 14:52

1

join = "SELECT users.name, users.room, attendance.clock_in FROM users " \
       "JOIN attendance ON users.id = attendance.user_id " \
       "WHERE attendance.clock_in >= CURDATE() " \
       "AND attendance.clock_in < CURDATE() + INTERVAL 1 DAY"

answered Jul 15 '20 at 14:52

Be Chiller Too

2,502
2
16
42

python mysql.connector selecting rows with current date in a JOIN clause

2 Answers2