基于服务器时间与ACF日期选择器时间计算剩余天数并过滤WordPress自定义SQL查询

Got it, let's tackle this problem step by step. Your goal is to narrow down the query to only return posts where the time left until promotion_duration_date is less than 10 days, right? Here's how to adjust your existing code to make that happen:

Key Context First #

I’m assuming your ACF promotion_duration_date is stored as a Unix timestamp (an integer representing seconds since the epoch) — this is the default if you set the ACF field’s return format to "Timestamp". We’ll use SQL’s built-in functions to compare this against the server’s current time.

Modified Query Code #

Here’s the updated version of your query with the filtering logic added:

$wpdb->get_results('
    SELECT goodies.ID, meta1.meta_value AS promotion_duration_date 
    FROM ' . $wpdb->prefix . 'posts AS goodies 
    INNER JOIN ' . $wpdb->prefix . 'postmeta meta1 
        ON meta1.post_id = goodies.ID 
        AND meta1.meta_key = "promotion_duration_date"
    WHERE 
        goodies.post_type = "goodies" 
        AND goodies.post_status = "publish"
        -- Make sure the promotion date hasn't already passed
        AND meta1.meta_value > UNIX_TIMESTAMP()
        -- Calculate remaining seconds: 10 days = 10 * 24 * 60 * 60 = 864000 seconds
        AND (meta1.meta_value - UNIX_TIMESTAMP()) < 864000
    ORDER BY goodies.ID 
');

What’s Different? #

1. Switched to INNER JOIN: Since we only care about posts that have a valid promotion_duration_date value, an inner join automatically excludes posts without this meta field. If you absolutely need to keep the LEFT JOIN, just add AND meta1.meta_value IS NOT NULL to the WHERE clause.
2. Added two critical conditions:
   • meta1.meta_value > UNIX_TIMESTAMP(): Filters out posts where the promotion date has already expired.
   • (meta1.meta_value - UNIX_TIMESTAMP()) < 864000: Calculates the time difference in seconds between the promotion timestamp and the server’s current time, then checks if it’s less than 10 full days (864000 seconds total).

If Your ACF Date is Stored as a String (e.g., Y-m-d) #

If you set the ACF field’s return format to "Date" (a string like 2024-12-31), you’ll need to convert it to a SQL-compatible date first. Here’s the adjusted WHERE clause for that scenario:

WHERE 
    goodies.post_type = "goodies" 
    AND goodies.post_status = "publish"
    AND STR_TO_DATE(meta1.meta_value, '%Y-%m-%d') > CURDATE()
    AND DATEDIFF(STR_TO_DATE(meta1.meta_value, '%Y-%m-%d'), CURDATE()) < 10

This uses CURDATE() to get the server’s current date, STR_TO_DATE to parse your ACF date string, and DATEDIFF to directly calculate the number of days between the two dates.

内容的提问来源于stack exchange，提问作者Milos N.