[wp-trac] [WordPress Trac] #41782: Using date_query with 'before' in WP_Query returns wrong timezone

[WordPress Trac]

#41782: Using date_query with 'before' in WP_Query returns wrong timezone
--------------------------+------------------------------
 Reporter:  Biranit       |       Owner:
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Date/Time     |     Version:  4.8.1
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:
--------------------------+------------------------------

Comment (by birgire):

 If I understand it correctly WordPress sets the PHP timezone to UTC:

 {{{
 // WordPress calculates offsets from UTC.
 date_default_timezone_set( 'UTC' );
 }}}

 and uses the {{{gmt_offset}}} option to calculate the offset.

 Trying to understand it better {{{WP_Date_Query::how
 build_mysql_datetime()}}} handles string dates, I ran this test function:

 {{{
 function dq_test_info( $datetime, $gmt_offset )
 {
         $time = time();
         $now = $time + $gmt_offset * 3600;

         print PHP_EOL;
         printf( "\$datetime: %s"
 . PHP_EOL, $datetime );
         printf( "date_default_timezone_get(): %s"
 . PHP_EOL, date_default_timezone_get() );
         printf( "\$gmt_offset: %s"
 . PHP_EOL, $gmt_offset );
         printf( "time(): %s"
 . PHP_EOL, $time );
         printf( "\$now = time() + \$gm_offset * 3600: %s"
 . PHP_EOL, $now );
         printf( "strtotime( \$datetime ): %s"
 . PHP_EOL, strtotime( $datetime ) );
         printf( "strtotime( \$datetime, \$now ): %s"
 . PHP_EOL, strtotime( $datetime, $now ) );
         printf( "date( 'Y-m-d H:i:s', strtotime( \$datetime ) ): %s"
 . PHP_EOL, date( 'Y-m-d H:i:s', strtotime( $datetime ) ) );
         printf( "date( 'Y-m-d H:i:s', strtotime( \$datetime, \$now ) ):
 %s"     . PHP_EOL, date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ) );
         printf( "gmdate( 'Y-m-d H:i:s', strtotime( \$datetime, \$now ) ):
 %s"   . PHP_EOL, gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ) );
 }

 }}}


 '''Test 1
 '''
 {{{
 $datetime = '2017-01-01 10:00:00';

 dq_test_info( $datetime, $gmt_offset = 0 );
 dq_test_info( $datetime, $gmt_offset + 1 );
 dq_test_info( $datetime, $gmt_offset + 2 );
 }}}

 with output:

 {{{
 $datetime: 2017-01-01 10:00:00
 date_default_timezone_get(): UTC
 $gmt_offset: 0
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504456206
 strtotime( $datetime ): 1483264800
 strtotime( $datetime, $now ): 1483264800
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-01-01 10:00:00
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00

 $datetime: 2017-01-01 10:00:00
 date_default_timezone_get(): UTC
 $gmt_offset: 1
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504459806
 strtotime( $datetime ): 1483264800
 strtotime( $datetime, $now ): 1483264800
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-01-01 10:00:00
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00

 $datetime: 2017-01-01 10:00:00
 date_default_timezone_get(): UTC
 $gmt_offset: 2
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504463406
 strtotime( $datetime ): 1483264800
 strtotime( $datetime, $now ): 1483264800
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-01-01 10:00:00
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-01-01 10:00:00
 }}}


 '''Test 2
 '''
 {{{
 $datetime = '-3 days';

 dq_test_info( $datetime, $gmt_offset = 0 );
 dq_test_info( $datetime, $gmt_offset + 1 );
 dq_test_info( $datetime, $gmt_offset + 2 );
 }}}

 with output:

 {{{
 $datetime: -3 days
 date_default_timezone_get(): UTC
 $gmt_offset: 0
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504456206
 strtotime( $datetime ): 1504197006
 strtotime( $datetime, $now ): 1504197006
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-08-31 16:30:06
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 16:30:06
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 16:30:06

 $datetime: -3 days
 date_default_timezone_get(): UTC
 $gmt_offset: 1
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504459806
 strtotime( $datetime ): 1504197006
 strtotime( $datetime, $now ): 1504200606
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-08-31 16:30:06
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 17:30:06
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 17:30:06

 $datetime: -3 days
 date_default_timezone_get(): UTC
 $gmt_offset: 2
 time(): 1504456206
 $now = time() + $gm_offset * 3600: 1504463406
 strtotime( $datetime ): 1504197006
 strtotime( $datetime, $now ): 1504204206
 date( 'Y-m-d H:i:s', strtotime( $datetime ) ): 2017-08-31 16:30:06
 date( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 18:30:06
 gmdate( 'Y-m-d H:i:s', strtotime( $datetime, $now ) ): 2017-08-31 18:30:06
 }}}

 @Biranit, it would be interesting to see how it runs on your install.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/41782#comment:8>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform