MySQL日期比较过滤器 [英] MySQL date comparison filter

问题描述

我有一些运行良好的SQL代码，可以从我的Wordpress数据库中返回所需的结果.

I have some SQL code that works great and returns the desired results from my Wordpress database.

但是，我根本无法理解如何过滤说话之间的日期:

However, I simply cannot get my head around how to filter dates between say:

2010-12-10 00:00:00

和

2010-12-15 00:00:00

这是我的SQL代码:

$SQL_K =  "SELECT SQL_CALC_FOUND_ROWS wp_posts.* 
             FROM wp_posts 
             JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)
            WHERE 1 = 1 
              AND wp_posts.post_type = 'post' 
              AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') 
              AND wp_postmeta.meta_key = 'expiry_date' 
         GROUP BY wp_posts.ID 
         ORDER BY wp_posts.post_date DESC 
            LIMIT 0, 100 ";

推荐答案

WHERE date_column BETWEEN STR_TO_DATE('2010-12-10', '%Y-%m-%d') AND STR_TO_DATE('2010-12-15', '%Y-%m-%d')

是这样吗?

忘记了引号.哎呀

EDITv2:使用更新后的查询添加代码

EDITv3:几乎没有优化 删除了1 = 1比较，对wp_posts.post_status使用IN()

EDITv3: little optimization removed 1=1 comparison, uses IN() for wp_posts.post_status

SELECT      SQL_CALC_FOUND_ROWS wp_posts.* 
FROM        wp_posts 
  JOIN      wp_postmeta 
  ON        (wp_posts.ID = wp_postmeta.post_id) 
WHERE       wp_posts.post_type = 'post' 
  AND       (wp_posts.post_status IN ('publish','private'))
  AND       wp_postmeta.meta_key = 'expiry_date' 
  AND       ___INSERT_NAME_OF_DATE_COLUMN_HERE___
    BETWEEN STR_TO_DATE('2010-12-05', '%Y-%m-%d')
      AND   STR_TO_DATE('2010-12-15', '%Y-%m-%d')
GROUP BY    wp_posts.ID 
ORDER BY    wp_posts.post_date DESC 
LIMIT       0, 100

这篇关于MySQL日期比较过滤器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！