get_sites() for sites registered before given date

Question

I am trying to retrieve a list of sites within my WordPress multisite instance that were registered before a given date/time.

Working with get_sites() and WP_Date_Query I have the following, but no sites are being returned...

$now       = current_time( 'timestamp' );
$lifetime  = epd_get_default_site_lifetime(); // returning 3600. Aim to retrieve all sites registered over an hour ago

if ( ! $lifetime )  {
    return;
}

$delete_on = $now - $lifetime;

$delete_sites_query = array(
    'site__not_in' => array( get_network()->blog_id ),
    'date_query' => array(
        array(
            'year'          => date( 'Y', $delete_on ),
            'month'         => date( 'n', $delete_on ),
            'day'           => date( 'j', $delete_on ),
            'hour'          => date( 'G', $delete_on ),
            'minute'        => intval( date( 'i', $delete_on ) ),
            'second'        => intval( date( 's', $delete_on ) ),
            'compare'       => '<=',
            'column'        => 'registered'
        )
    )
);

$delete_sites_query = apply_filters( 'epd_delete_sites_query', $delete_sites_query );

$sites = get_sites( $delete_sites_query );

If I var_export( $delete_sites_query ); I see the following (which appears to be correct to me)...

array (
  'site__not_in' => 
  array (
    0 => '1',
  ),
  'date_query' => 
  array (
    0 => 
    array (
      'year' => '2018',
      'month' => '8',
      'day' => '14',
      'hour' => '22',
      'minute' => 46,
      'second' => 19,
      'compare' => '<=',
      'column' => 'registered',
    ),
  ),
)

I have sites registered prior to the date above...

Answer 1

Here, this should permanently delete all sites older than 30 days (except the one with ID 1). I was not able to make it work with date_query.

If you need to call get_sites() for these sites for some other reason, just instead of deleting them, add their IDs in an array, and then call get_sites() only for sites in this array. To get more sites than 100 you need to change the number argument, as I have done below.

$delete_on_timestamp = time() - 3600*24*30;

$args = array (
    'site__not_in' => array ( 1 ),
    'fields' => 'ids',
    'number' => 999999,
);

$site_ids = get_sites( $args );

foreach( $site_ids as $site_id ) {
    $object_site_details = get_blog_details( $site_id );
    $site_register_timestamp = strtotime( $object_site_details->registered );
    if ( $site_register_timestamp <= $delete_on_timestamp ) {
        wpmu_delete_blog( $site_id, true );
    }
}

Keep in mind that the registered time is stored in the timezone of the main site, unlike the last updated time, which uses GMT time. I have reported this problem here and it should be fixed.

Also keep in mind that if you have a lot of sites, this code will timeout. To fix this you can do this several times in smaller chunks, by using the offset and the number argument.