[wp-trac] [WordPress Trac] #63662: DateTimePicker in the post editor does not show posts that are published on midnight of first day of month

[WordPress Trac]

#63662: DateTimePicker in the post editor does not show posts that are published on
midnight of first day of month
-------------------------------+------------------------------
 Reporter:  butesa             |       Owner:  (none)
     Type:  defect (bug)       |      Status:  new
 Priority:  normal             |   Milestone:  Awaiting Review
Component:  Posts, Post Types  |     Version:  6.8
 Severity:  normal             |  Resolution:
 Keywords:  has-patch          |     Focuses:
-------------------------------+------------------------------

Comment (by butesa):

 I did some research on why this bug depends on the time zone setting.

 The REST request that is sent by the browser uses the timezone setting of
 the browser (aka system time zone of the user) to calculate the UTC
 representation of 'midnight'. On the server side, this UTC value gets
 converted to the time zone that is selected in the wordpress settings.

 So for example, my browsers time zone ist UTC+2 (Central European Summer
 Time), so the REST request for August 2025 always contains this data,
 regardless of the wordpress time zone setting:
 {{{
 after=2025-07-31T22:00:00.000Z&before=2025-08-31T21:59:59.999Z
 }}}

 If I select UTC+1 as wordpress time zone (or any other time zone that is
 lower than my browsers time zone), this results in the following SQL
 query, which makes the blue dot for August 1st visible. But if you created
 a post with publishing date 2025-08-31 23:30:00, the blue dot would
 **not** be visible.
 {{{
 wp_posts.post_date < '2025-08-31 22:59:59' AND wp_posts.post_date >
 '2025-07-31 23:00:00'
 }}}

 If I select UTC+2 as wordpress time zone (my browsers time zone), this
 results in the following SQL query, which makes the blue dot for August
 1st **not** visible. This problem is solved by the suggested pull request,
 which simply replaces < and > by <= and >=.
 {{{
 wp_posts.post_date < '2025-08-31 23:59:59' AND wp_posts.post_date >
 '2025-08-01 00:00:00'
 }}}

 If I select UTC+3 as wordpress time zone (or any other time zone that is
 higher than my browsers time zone), this results in the following SQL
 query, which makes the blue dot for August 1st **not** visible. This
 problem is **not** solved by the suggested pull request.
 {{{
 wp_posts.post_date < '2025-09-01 00:59:59' AND wp_posts.post_date >
 '2025-08-01 01:00:00'
 }}}

 So in fact, there are two bugs:
 - The SQL query uses the wrong operators (< and > instead of <= and >=),
 which affects midnight-planned posts only if the time zone of the browser
 is the same as the wordpress time zone. This bug is in the server side PHP
 code and is fixed by the proposed pull request.
 - The date limits in the REST request are calculated wrong (wordpress time
 zone should be used) if the time zone of the browser is not the same as
 the wordpress time zone. This bug seems to be in the client side
 Javascript code.

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