1

As per the title, what I want to do is run a custom query that (using WP-Cron) will run once a day to check for users who registered exactly 360 days before the current date (don't need time, just date) and gather their ID. I have this:

global $wpdb;
$sql = $wpdb->prepare(
    "SELECT * FROM {$wpdb->users} 
     WHERE {$wpdb->users}.user_registered = CURRENT-DATE-MINUS-360-DAYS";
);
$userdata = HOW-DO-I-GET-THE-RESULTS-IN-AN-ARRAY

Where the bit in caps represents the bit I have no idea what to do :) Any help much appreciated.

3
2

I peeked into the WP_User_Query class and it supports a WP_Date_Query query on the user registration date.

So we could use:

$query = new WP_User_Query( $args );

or simply:

$users = get_users( $args );

where:

$args = [
    'fields'     => 'ID',
    'number'     => 8,
    'date_query' => [
        [ 'before' => '359 days ago midnight' ],
        [ 'after'  => '360 days ago midnight', 'inclusive' => true ],
   ] 
];

This generates the following SQL query (expanded view):

SELECT wp_users.ID 
    FROM wp_users 
    WHERE 1=1 
        AND ( 
                wp_users.user_registered >= '2014-08-10 00:00:00' 
            AND       
                wp_users.user_registered < '2014-08-11 00:00:00' 
        ) 
    ORDER BY user_login ASC 
    LIMIT 10;

where today is 2015-08-05.

It looks like we should update the Codex on get_users() function, regarding the date_query argument.

6
  • It looks like we should update the Codex Looks like a job specially for you :-) Aug 6 '15 at 17:37
  • 1
    it looks like I've been hired for that job ;-) @PieterGoosen
    – birgire
    Aug 6 '15 at 18:04
  • I've updated the date_query info on the get_users() page in the Codex. This was also missing on the WP_User_Query page, so I added a new section there with an example. @PieterGoosen
    – birgire
    Aug 6 '15 at 19:48
  • Great, that should help many others out there. Can't upvote you again for that though, hahaha, but great work. ;-) Aug 6 '15 at 19:51
  • Hopefully it will help someone out there - they could even enhance it or correct it ;-)
    – birgire
    Aug 6 '15 at 19:53
0

Use

$wpdb->get_results("SELECT *, (DATEDIFF(NOW(),user_registered)) AS daydiff FROM {$wpdb->users} WHERE 'daydiff' = 360");
1
  • Hi TrangDinhVan, thanks for your reply - I'll have a go with that and let you know how it goes :)
    – Kev
    Aug 5 '15 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.