SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL Datetime Query

    Hi all,

    I have a cell in a mysql table formated for datetime and named Datetime. I want to select all the entries in that table for a certain month, let's use March for testing purposes. Here is my query...

    mysql_query("SELECT * FROM stats WHERE Datetime = March")

    I have no clue how to write it, any help is greatly appreciated.

    Thanks,
    David

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    WHERE month(`Datetime`)=3

    it's not a good idea to name your column using a reserved word like datetime, you will forever have to remember the backticks
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Works like a charm, now if I needed to select reseult where Datetime = Today what would I do? I've tried:

    "SELECT * FROM stats WHERE date(`Datetime`)='" . date("Y-n-d") . "'"
    But it doesn't seem to work.

    Thanks,
    Dave

  4. #4
    SitePoint Evangelist
    Join Date
    Feb 2004
    Location
    Sofia, Bulgaria
    Posts
    421
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try this:
    PHP Code:
    "SELECT * FROM stats WHERE date(`Datetime`)=CURDATE()" 
    more info on MySQL datetime functions:
    http://www.mysql.com/documentation/m...time_functions

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That gives me Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

  6. #6
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Use mysql_error to get an error message about why a query failed.

    PHP Code:
    <?php

    $sql 
    "SELECT * FROM stats WHERE date(`Datetime`)=CURDATE()";
    $result mysql_query($sql) or die( mysql_error() );

    ?>

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I have renamed the collumn from Datetime to RecordTime to prevent any errors, here is the mysql error:

    You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(RecordTime)=CURDATE()' at line 1

  8. #8
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    Írebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You probably have a MySQL version < 4.1.1 which means that the function DATE() isn't available to you. Try this:

    PHP Code:
    "SELECT * FROM stats WHERE DATE_FORMAT( RecordTime , '%Y-%m-%d' ) = CURDATE()" 

  9. #9
    SitePoint Zealot
    Join Date
    Sep 2002
    Posts
    180
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That did the trick, thanks a million!

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if it matters, you should be aware that by performing a date function on a datetime column, you are probably eliminating the possibility of the optimizer utilizing an index on that column -- and this rule of thumb holds for all databases i'm familiar with

    therefore, do not write

    ... where xxxx(datecol) = something

    but instead, write

    ... where datecol = yyyy(something)


    so how do you select rows of a datetime column which correspond to today?

    what you want to do is bracket them by midnight times

    simple, eh?

    in mysql,
    Code:
     where datecol 
           between current_date 
               and date_add(
                   current_date
                 , interval 1 day
                           )
    the endpoints of the range are calculated by the optimizer once, prior to query execution, and then these values can be used to delimit an index search which is very fast

    (compared to a table scan which is very slow, when the index is not utilized)

    note that the BETWEEN syntax includes midnight at either end of the range

    if you are legitimately concerned about not including midnight from tomorrow, change it to:
    Code:
     where datecol >= current_date 
       and datecol <  date_add(
                      current_date
                    , interval 1 day
                              )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •