CODEWITHSUNDEEP
phpmysqlsqlwordpressdatetime
Ry Van
Ry Van

Reputation: 349

Mysql Select record from now to next 7 days

Hello there i am creating a function in wordpress and i need to get records beetween now and next 7 days. i have tried few queries.but couldn't get the result.

SELECT * from wp_postmeta where wp_postmeta.meta_key = '__rp_order_date';

The query above returns 

meta_id | post_id | meta_key        | meta_value
--------+---------+-----------------+-------------------
7023    | 2979    | __rp_order_date | 2018-03-26 12:00 AM

but when i filter by date it doesn't display any records.

method 1

SELECT * from wp_postmeta where wp_postmeta.meta_key = '__rp_order_date' and date(wp_postmeta.meta_value) BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY);

method 2

SELECT * from wp_postmeta where wp_postmeta.meta_key = '__rp_order_date' and date(wp_postmeta.meta_value) <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)

My actual intention is to execute this code,which also displays nothing.

SELECT DISTINCT wp_woocommerce_order_items.order_id,wp_posts.post_status,wp_postmeta.meta_value as delivery_date from wp_woocommerce_order_items 
INNER JOIN wp_posts on wp_posts.ID = wp_woocommerce_order_items.order_id 
INNER JOIN wp_postmeta ON wp_postmeta.post_id = wp_woocommerce_order_items.order_id 
where wp_posts.post_status='wc-processing' and wp_postmeta.meta_key = '__rp_order_date' and DATE(wp_postmeta.meta_value) <= DATE_ADD(CURDATE(), INTERVAL 7 DAY)

I couldn't figure it out, thanks in advance.

Upvotes: 1

Views: 349

Answers (1)

Jay Shankar Gupta
Jay Shankar Gupta

Reputation: 6088

In Place of date(wp_postmeta.meta_value) Use STR_TO_DATE(wp_postmeta.meta_value, '%Y-%m-%d')

You Can use like this also, You have to CONCAT :00 in your time to convert varchar to datetime

SELECT * 
FROM wp_postmeta 
WHERE wp_postmeta.meta_key = '__rp_order_date' 
AND STR_TO_DATE(CONCAT(SUBSTRING(wp_postmeta.meta_value,1,16),':00 ',SUBSTRING(wp_postmeta.meta_value,18,2)),'%Y-%m-%d %r') 
BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 7 DAY);

Live Demo

http://sqlfiddle.com/#!9/748788/9

Upvotes: 1

Related Questions