SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Hybrid View

  1. #1
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    mysql LEFT function

    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

    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.

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

  2. #2
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by afridy View Post
    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

    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

    Code:
    $query = "SELECT * from sms_logs WHERE LEFT(date,10)='$dt'";
    works fine for me.

    Sorry for the trouble

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afridy View Post
    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 --
    Code:
    WHERE `date` >= '2012-08-13'
      AND `date`  < '2012-08-14'
    please let me know if you don't understand what that does
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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 --
    Code:
    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 =) ?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,268
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by afridy View Post
    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

    Quote Originally Posted by afridy View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Guru afridy's Avatar
    Join Date
    Mar 2007
    Posts
    966
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •