ThoseKind ThoseKind - 1 year ago 68
MySQL Question

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

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
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.

Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download