I have a MySql database that uses a timestamp field to track when the entry was inserted.

Now I want to query the table for entries by a date range.

I would prefer to use MySql to extract the records instead of extracting all records then iterating and comparing dates in php.

So I have a start-date and an end-date. I have tried YYYYMMDD and YYYY-MM-DD format with no success on these two kinds of queries:

// this doesn't appear to be working
SELECT COUNT(*) FROM table_name 
WHERE DATE(timestamp_row) BETWEEN '{$date1}' AND '{$date2};

// this doesn't appear to be working either
SELECT COUNT(*开发者_运维技巧) FROM table_name 
WHERE DATE(timestamp_row) >= {$date1}
AND DATE(timestamp_row) <= {$date2};

It appears that the Mysql DATE() function turns the timestamp into this format: YYYY-MM-DD

Is there a way to do this.

Which field types of these are you using? 1. DATETIME, 2. DATE, 3. TIMESTAMP

Since SELECT COUNT(*) FROM table_name WHERE DATE(timestamp_row) BETWEEN '{$date1}' AND '{$date2}; looks ok, if you use specific values instead of '{$date1}' AND '{$date2}; maybe there is something wrong with the way time is formatted in your rows?

You can automatically convert an invalid date to the valid equivalent like this:

SELECT '2008-02-31' + INTERVAL 0 DAY;

2008-03-02

You can try this

SELECT COUNT(*) FROM table_name WHERE timestamp_row BETWEEN '2011-01-01 00:00:00' AND '2011-01-31 23:59:59';

There's no need to use MySQL's DATE function in your original query.

You are doing the B'Day dates of users and thus hope this would be do the trick of formatting and selection of date

select id from my_table
 where
 timestamp < date_format(date_add(CURRENT_TIMESTAMP(), interval 1 day),
             '%Y%m%d000000' ) AND timestamp >=  date_format(CURRENT_TIMESTAMP(),
             '%Y%m%d000000')

Isearch the data by id but the date is wrong...This show 1970/01/01

                             $book_year= Date("Y", strtotime($year));
            $book_month= Date("m", strtotime($month));
            $book_day=Date("d", strtotime($day));
            $book_date=$year."/". $month."/". $day;
                    $book_date= Date("Y-m-d", strtotime($book_date));