MySql Query For Date Range in PHP

0
votes

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.

4
phpmysqldate
If you provided YYYY-MM-DD strings like that, enclosed in single quotes, the query would work as you want. There's something else wrong here that I can't see. Is timestamp_row not a TIMESTAMP/DATETIME/DATE type column? – Dan Grossman
Did you try it with actual date values, instead of {$date1}, etc? – sdleihssirhc
@dan timestanp_row is a the mysql type timestamp. I will try using static numbers to see if that works .. – OneNerd
@dan and @sdleihssirhc -- works with static date values, so will have to evaluate how I am getting those dates into values (problem is elsewhere). Thanks! – OneNerd
Note that using DATE() in all your queries will prevent any use of indexes on the timestamp column. Your whole table will have to be scanned to resolve the query. If you can, compare the column directly to a date with time. – Dan Grossman

4 Answers

0
votes

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

0
votes

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.

0
votes

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')
0
votes

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));