MySql date range query not showing all records

date formatMySQLphpmyadmin

I am working on mysql query right now. I write a query from fetch data from march 2018 to march 2019. There are more than 5000 records for this year. I write a query with date range from march 2018 to march 2019 but its showing me only 150 records

One more thing here. The date column in database is varchar. I know its not good but it was taken accidently during creation of database and I am not able to change this to date type because of lots of data in database

Here is the query I created

SELECT  a.*,b.* 
FROM OrderCalculation a 
right join crm_order b on a.orderid = b.orderno 
WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR 
AND str_to_date(b.Date,'%Y-%m-%d')  <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

Please make this query correct if you think something wrong in it

Thank you

Best Answer

The date column in database is varchar.

You should really fix that. If the string always looks like yyyy-mm-dd, then it will happen to work as a date. (And my code below will work.) That is, the string version of a DATE or DATETIME is handled almost identically the same as the proper datatype. One major difference is: "2019-08-01" versus "2019-8-1"

Do not hide a column (Date) inside a function call (str_to_date)

WHERE str_to_date(b.Date,'%Y-%m-%d') >= str_to_date(concat(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')-INTERVAL 1 YEAR 
  AND str_to_date(b.Date,'%Y-%m-%d') <= str_to_date(CONCAT(YEAR(CURDATE()),'-08-01'),'%Y-%m-%d')

-->

WHERE b.Date >= CURDATE() - INTERVAL 1 YEAR
  AND b.Date  < CURDATE() + INTERVAL 1 DAY

Did you really want 365 days, plus today? This disagrees with your statement of "from march 2018 to march 2019". (Plus March is 03, not 08.)

Now, INDEX(Date) may help with the speed of the query. I say only "may" because the index will be ignored if the year is most of the table; it would be faster to ignore the table and simply scan the data. (And my version will be as slow as yours).

Please rearrange your query to use LEFT JOIN instead of RIGHT JOIN. My brain has trouble with RIGHT JOIN.

Related Solutions

MySQL – Why is Data Being Cached on a Dynamic PHP Site?

You could do SELECT SQL_NO_CACHE. This is used by mysqldump to always dump fresh data to a text file. This may also help flush the innodb buffer pool if the table being SELECTed is InnoDB.

If you cannot maniuplate the SELECT statements, then set query_cache_type to 0. That will make all SELECTs behave like SELECT SQL_NO_CACHE. You can do this without restarting mysql:

Step 1) Add this to the /etc/my.cnf

[mysqld]
query_cache_type=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;

FINAL WORD

Doing this may increase read I/O. You may also want to consider setting innodb_max_dirty_pages_pct = 0 to keey the innodb buffer pool with the freshest data that is as fully flushed to disk as possible.

Step 1) Add this to the /etc/my.cnf

[mysqld]
query_cache_type=0
innodb_max_dirty_pages_pct=0

Step 2) Run this in mysql client

SET GLOBAL query_cache_type = 0;
SET GLOBAL innodb_max_dirty_pages_pct = 0;

UPDATE 2011-05-20 11:17

It is possible all the DB Connections have timed out from PHP's vantage point.

See if restarting MySQL and apache will do it.

Also, make sure that the timeout values for MySQL, PHP, and Apache all match up

Also, do this

netstat | grep [m3][y3][s0][q6] | grep TIME_WAIT

This helps see if any dead DB Connections have not been release by the OS

Decode query truncates a date

As a_horse indicated, select min(date)... is sufficient. The DECODE and multiple conversions just slow things down without accomplishing anything. Even as parts of queries, you should eliminate this needless processing.

As Phil indicated, the displaying of dates is controlled by the NLS_DATE_FORMAT parameter and can be altered to display dates as you wish. This does change how the queries run (unless there are missing format specifications or SQL injection is being done). Change the display format like this:

 ALTER SESSION SET NLS_DATE_FORMAT='YYYYMMDD';

When doing a to_date on a two digit year, use YY when you want the first two digits of the year to match the current year, but use RR when you want the first two digits to be 20 for years ending in 0-49 and 19 for years ending in 50-99. See the SQL Language Reference for more information:

The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.

If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.

That is:

If the specified two-digit year is 00 to 49, then

If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.

If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

If the specified two-digit year is 50 to 99, then

If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.

If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

The following examples demonstrate the behavior of the RR datetime format element.
SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
---- 
1998 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 
Year
----
2017

Best Answer

Related Solutions

MySQL – Why is Data Being Cached on a Dynamic PHP Site?

Decode query truncates a date

Related Question