2

I try to get the post_ids from postmeta where the _sale_price_dates_to are less the now. And I did something like this:

global $wpdb;
$publishid = $wpdb->get_results('SELECT meta_value FROM ' . $wpdb->postmeta . ' WHERE meta_key="_sale_price_dates_to" and meta_value<=' .time().'') or die("Error: Cannot create object");

But it doesn't work. Maybe because the _sale_price_dates_to has format longtext not date. how to change it in the query?

But It's not over. I want to add the condition that the products have to be published then I have to join:

$publishid = $wpdb->get_results('SELECT meta_value FROM ' . $wpdb->postmeta . 'join' . $wpdb->posts . ' ON wp_posts.ID = wp_postmeta.post_id WHERE wp_postmeta.meta_key="_sale_price_dates_to" and wp_postmeta.meta_value<=' .time().' and wp_posts.post_status="publish" ') or die("Error: Cannot create object");

But because of the first problem, I don't know it works.

LoicTheAztec
  • 229,944
  • 23
  • 356
  • 399
yoomla
  • 75
  • 8

1 Answers1

0

Update: Setting up the right time zone in the code

Try the following to make a functional SQL query using WPDB to get the Post IDs where post meta _sale_price_dates_to timestamp is less the now time stamp:

global $wpdb;

// Set the correct time zone  (http://php.net/manual/en/timezones.php)
date_default_timezone_set('Europe/Paris');

// An array of IDs
$results = $wpdb->get_col("
    SELECT p.ID
    FROM {$wpdb->prefix}posts AS p
    JOIN {$wpdb->prefix}postmeta AS pm ON p.ID = pm.post_id
    WHERE p.post_status = 'publish'
    AND pm.meta_key = '_sale_price_dates_to'
    AND pm.meta_value <= '".time()."'
    AND pm.meta_value != ''
");

// Raw Output (array of post IDs)
print_r($results);

Tested and works.

meta_value != '' is needed to avoid Post IDs with empty values to be queried.

LoicTheAztec
  • 229,944
  • 23
  • 356
  • 399
  • Hi there, After a few days using the code, I saw one small problem. When the hour of pm.meta_value is ie. 05:11:00 and time() is 17:05:00 it doesn't work. It seems the 05 is not 5 am but should be. And I have a 12 hours shift. Do you have any idea how to solve it? – yoomla Nov 02 '18 at 16:55
  • I clicked the link 'accept the answer' first time. I'm new on this site and maybe I made it not in proper way. Maybe now – yoomla Nov 02 '18 at 18:15
  • @yoomla Ok thanks, you just do it well. I just updated my code now. You will need to set the correct [time zone](http://php.net/manual/en/timezones.php) in this code for `date_default_timezone_set('Europe/Paris');` – LoicTheAztec Nov 02 '18 at 18:29
  • Almost, there is one hour shift. I set up correct timezone for my city. – yoomla Nov 03 '18 at 09:19
  • LoicTheAztec could you take a look: https://stackoverflow.com/questions/53522073/get-products-ids-by-sale-price-dates-to-except-orig-offer-product-id-before-2we?noredirect=1#comment93912827_53522073 – yoomla Nov 28 '18 at 17:19