mysql LEFT function

[afridy]

Hai folks,

i am struggling to use the LEFT function in the below scenario :

sms_logs table :

date
+---------+
2012/08/11 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/14 11:03:27 AM
2012/08/15 11:03:27 AM

i want to retrieve all the rows recorded on 2012/08/13

so i am using the below code

$dt='2012/08/13';
$query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";

The above query returns 0. whats wrong?


edited :

also tried below and failed.

$query = "SELECT LEFT(date,10) from sms_logs WHERE date='$dt'";

[afridy]

Hai folks,

i am struggling to use the LEFT function in the below scenario :

sms_logs table :

date
+---------+
2012/08/11 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/13 11:03:27 AM
2012/08/14 11:03:27 AM
2012/08/15 11:03:27 AM

i want to retrieve all the rows recorded on 2012/08/13

so i am using the below code

$dt='2012/08/13';
$query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";

The above query returns 0. whats wrong?


[/code]

Sorry folks,
i messed up a bit.
i have shown you $dt='$dt='2012/08/13';
actually i am using $dt=date('Y-m-d');
the problem really was i was using - insted of / in the date() function.
so now

$query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";

works fine for me.

Sorry for the trouble

[r937]

works fine for me.

then you are lucky that mysql is such a forgiving, lenient database

dates are not strings -- please do not use string functions on them, for two reasons

first, if you do use a string function (like LEFT), you force an implicit conversion of the column value from datetime to string

this leaves open the possibility that the conversion to string does not meet the format that you're expecting, and your code will fail

but second, and far more important, when you apply a function to a column value like that, the database has no choice but to do a table scan, which is very inefficient, even if that column has an index on it for optimization

the "best practice" for what you're trying to do is an open-ended range test --

WHERE `date` >= '2012-08-13'
  AND `date`  < '2012-08-14'

please let me know if you don't understand what that does

[afridy]

then you are lucky that mysql is such a forgiving, lenient database

dates are not strings -- please do not use string functions on them, for two reasons

first, if you do use a string function (like LEFT), you force an implicit conversion of the column value from datetime to string

this leaves open the possibility that the conversion to string does not meet the format that you're expecting, and your code will fail

but second, and far more important, when you apply a function to a column value like that, the database has no choice but to do a table scan, which is very inefficient, even if that column has an index on it for optimization

the "best practice" for what you're trying to do is an open-ended range test --

WHERE `date` >= '2012-08-13'
  AND `date`  < '2012-08-14'

please let me know if you don't understand what that does

Thanks for the valuable comments

WHERE `date` >= '2012-08-13'
AND `date` < '2012-08-14'

Small doubt on ur comment.

in actual coding i use $dt=date('Y/m/d'); not really 2012-08-13. that in mind..

the above example you give looks like again a string comparison for me? not require to convert the dates to time stamp or anything on both sides (to the =) ?

[r937]

in actual coding i use $dt=date('Y/m/d'); not really 2012-08-13. that in mind..

no problem, mysql will recognize that easily... also '2012#08#13' and '2012@08@13' et cetera

the above example you give looks like again a string comparison for me? not require to convert the dates to time stamp or anything on both sides (to the =) ?

no, it's not a string comparison

mysql recognized that your `date` column is a datetime, and so it converts '2012-08-13' (which, you are correct, is a string) to datetime format, and then it does a datetime comparison

by the way, `date` is a poor choice for a column name, because DATE is a reserved word, and you will often get an error message if you forget to use those pesky backticks

[afridy]

no problem, mysql will recognize that easily... also '2012#08#13' and '2012@08@13' et cetera

no, it's not a string comparison

mysql recognized that your `date` column is a datetime, and so it converts '2012-08-13' (which, you are correct, is a string) to datetime format, and then it does a datetime comparison

by the way, `date` is a poor choice for a column name, because DATE is a reserved word, and you will often get an error message if you forget to use those pesky backticks

Thanks r937.
i understand. btw, my date and time column is VARCHAR 255.

[r937]

btw, my date and time column is VARCHAR 255.

o rly?

in that case, disregard everything i said in this thread until you change it to DATETIME

[afridy]

o rly?

in that case, disregard everything i said in this thread until you change it to DATETIME

really sorry for not mentioning that

[afridy]

no problem, the data bese is just built. i can change the filed to DATETIME and follow your instructions even for a better performance.