Search code examples
mysqlselectsql-like

SELECT all_data WHERE value_in_column LIKE specific_string

I have a MySQL database with a table that contains a lot of data for a certain vehicle across many different days. In this table there is a column called time_stamp that has the date the data was taken like so:

ID   UnitID   time_stamp         data   more_data
0    56       2016-08-10 12:01   asdf   12
1    57       2016-08-09 11:19   ghjk   34
2    57       2016-08-09 9:35    qwer   56
3    58       2016-08-09 1:16    tyui   78

I want to select everything in the table where UnitID LIKE 57 and time_stamp LIKE 2016-08-09% so that it would return:

ID   UnitID   time_stamp         data   more_data
1    57       2016-08-09 11:19   ghjk   34
2    57       2016-08-09 9:35    qwer   56

I have tried SELECT * FROM table_name WHERE time_stamp LIKE 2016-08-09% AND UnitID LIKE 57; but I keep getting a Error Code: 1054 telling me that there is an unknown column in 'where clause'. I am still very new to MySQL but I can usually figure out what I need to do with the help of the MySQL Docs, however this one I cannot figure out. I have also looked for similar questions like this one here, but nothing I have found is able to accomplish exactly what I want.

Solution

  • LIKE is for string matching and as such is comparatively slow to compared to more appropriate operators. Your query would be better with a WHERE such as this:

    WHERE UnitID = 57 AND time_stamp BETWEEN '2016-08-09 00:00:00' AND '2016-08-09 23:59:29'

    That said, MySQL's main objection to the query you had was probably that you did not enclose 2016-08-09% in quotes.