John Davidson

Message:

I want to filter my custom query by compare server (SQL) time and timestamp from the database ACF datepicker in my case

I have a query here:

$wpdb->get_results('SELECT goodies.ID, meta1.meta_value AS promotion_duration_date FROM ' . $wpdb->prefix . 'posts AS goodies

    LEFT 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" 

    ORDER BY goodies.ID

    ');

My goal is to calculate how many days are left between the promotion_duration_date variable and server time, and if is that number less than on example 10, run query?

---

My Answer:
> You can calculate the number of days left by comparing the current server time with the timestamp from the ACF datepicker field in your custom query. Here's an example of how you can do this:

php
$current_time = current_time('timestamp'); // Get the current server time in timestamp format

$args = array(
'post_type' => 'your_post_type',
'meta_query' => array(
array(
'key' => 'your_datepicker_field', // Replace with your ACF datepicker field key
'value' => $current_time,
'compare' => '>',
'type' => 'NUMERIC'
)
)
);

$query = new WP_Query($args);

if ($query->have_posts()) {
while ($query->have_posts()) {
$query->the_post();
// Output your post content here
}
} else {
// No posts found
}

wp_reset_postdata();


In this code snippet, we first get the current server time in timestamp format using the `current_time()` function. We then set up a custom query using `WP_Query` and include a `meta_query` parameter to filter posts based on the ACF datepicker field value being greater than the current server time.

Make sure to replace `'your_post_type'` with the actual post type you want to query and `'your_datepicker_field'` with the key of your ACF datepicker field.

This code will retrieve posts where the date in the ACF datepicker field is in the future compared to the current server time.