1

I'm trying to use date_query to query posts with todays date or a future date. Think events, I don't want to show any posts which has a date before todays date. I'm using pre_get_posts and setting the query like so:

$query->set( 'post_status', array( 'publish', 'future' ) );
$query->set( 'date_query', array(
    array(
        'year'    => date( 'Y' ),
        'month'   => date( 'm' ),
        'day'     => date( 'd' ),
        'hour'    => date( 'G' ),
        'minute'  => date( 'i' ),
        'compare' => '>='
    )
) );
Improve this question

1 Answer 1

Reset to default
3

I wonder if you mean this kind of date_query:

$query->set( 'date_query', [
    [
        'after'     => 'today midnight',
        'column'    => 'post_date_gmt',
        'inclusive' => true,
    ],
] );

where we use the after attribute.

Here the after date will be calculated by WP_Date_Query as:

gmdate( 'Y-m-d H:i:s', strtotime( 'today midnight', current_time( 'timestamp' ) ) );

With the column set as post_date_gmt, it generates to the following SQL condition:

wp_posts.post_date_gmt >= '2015-04-06 00:00:00'

on 6th of April 2015.

Update - Some Tests:

I ran some tests, trying to understand better the string datetime generation of WP_Date_Query:

wpse_test( 
    time(), 
    'GMT', 
    'today',
    'Y-m-d H:i:s',
    '#1 Using time() for blog with UTC-7 and PHP timezone as GMT'
);
wpse_test( 
    time(), 
    'US/Pacific', 
    'today',
    'Y-m-d H:i:s',
    '#2 Using time() for blog with UTC-7 and PHP timezone as US/Pacific'
);
wpse_test( 
    current_time( 'timestamp' ) , 
    'GMT', 
    'today',
    'Y-m-d H:i:s',
    '#3 Using current_time() for blog with UTC-7 and PHP timezone as GMT'
);
wpse_test( 
    current_time( 'timestamp' ) , 
    'US/Pacific', 
    'today',
    'Y-m-d H:i:s',
    '#4 Using current_time() for blog with UTC-7 and PHP timezone as US/Pacific'
);

where:

/**
 * Test the string datetime generation in WP_Date_Query 
 * with a given timezone and current time
 */

function wpse_test( $time, $timezone, $timestring, $format, $title )
{
    date_default_timezone_set( $timezone );

    printf( '--- %s ---', $title );
    echo PHP_EOL;
    echo date_default_timezone_get();
    echo PHP_EOL;
    echo $time;
    echo PHP_EOL;
    echo date( $format, $time );
    echo PHP_EOL;
    echo gmdate( $format, $time );
    echo PHP_EOL;
    echo date( $format, strtotime( $timestring, $time ) );
    echo PHP_EOL;
    echo gmdate( $format, strtotime( $timestring, $time ) );
    echo PHP_EOL;
}

The result is the following output:

--- #1 Using time() for blog with UTC-7 and PHP timezone as GMT ---
GMT
1428412051
2015-04-07 13:07:31
2015-04-07 13:07:31
2015-04-07 00:00:00
2015-04-07 00:00:00
--- #2 Using time() for blog with UTC-7 and PHP timezone as US/Pacific ---
US/Pacific
1428412051
2015-04-07 06:07:31
2015-04-07 13:07:31
2015-04-07 00:00:00
2015-04-07 07:00:00
--- #3 Using current_time() for blog with UTC-7 and PHP timezone as GMT ---
GMT
1428386851
2015-04-07 06:07:31
2015-04-07 06:07:31
2015-04-07 00:00:00
2015-04-07 00:00:00
--- #4 Using current_time() for blog with UTC-7 and PHP timezone as US/Pacific ---
US/Pacific
1428386851
2015-04-06 23:07:31
2015-04-07 06:07:31
2015-04-06 00:00:00
2015-04-06 07:00:00

We can see that time() doesn't change with the timezone and is in GMT.

So it seems that we have to be carefule regarding the timezones!

Improve this answer
8
  • 1
    I was thinking about the same approach but using current_time( 'Y-m-d' ) to compare with local time. What do you think? I'm always confused about local time/gmt/db time.
    – cybmeta
    Commented Apr 6, 2015 at 19:48
  • 1
    I see, it uses strtotime()
    – Howdy_McGee ♦
    Commented Apr 6, 2015 at 19:55
  • 1
    @Howdy_McGee yes it does and it supports many handy date-time notations ;-)
    – birgire
    Commented Apr 6, 2015 at 19:58
  • 1
    It's like opening the Pandora's box trying to dig into this and understand the effect of the timezones ;-) I ran some tests and the conclusion is that we have to be careful with the timezones, no surprize there, but I will keep this answer open and study this further ;-) @cybmeta
    – birgire
    Commented Apr 7, 2015 at 13:18
  • 2
    I guess I'm "lucky" in that respect, living in the 'Europe/Reykjavik' timezone that is GMT+0 and is without any summertime changes ;-) @cybmeta
    – birgire
    Commented Apr 7, 2015 at 13:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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