How to compare Event period with Week period using get_posts(), meta_query, relation and compare

Question

I have many posts, each post is an event with a period . This period can be 3 hours, 3 days, 15 days, 3 month. I defined the start time of event with start_timestampevents and the end with end_timestampevents. These data come from postmeta. I have four buttons : "first week", week +1, week +2 and year list. For the moment I'm focusing on the "current week"

Until now I thought my meta_query do what I wanted but it's not. Because in my meta_query ('meta_query' => array( array())) I compared the start_timestampevents ('key'=>) with the period of first day until the last day of current week ('value'=>). I did the same comparison with end_timestampevents.

Assuming that 'value' => array($firstdayweek_ts,$lastdaymidnight_ts) get the date (timestamp) of each day between 18 to 25 September, I'm not comparing things in the right way.

I keep the week from 18 to 25 September 2023 and event from 12 to 25 September for below example *.

My goal is to compare each timestamp/date of the event period (12,13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) with these of week range (18, 19, 20, 21, 22, 23, 24).

Until now, my events have only been displayed for the range of days in this week* if the start and end dates of this event are within it. Start and end dates outside (before or after) this weekly period are ignored, but the event always takes place during this week.

According to wp documentation, WP_Query or get_posts() doesn't allow to use array inside 'key'=> parameter .

I need to compare all the days (timestamps) of the event with the range of days in the current week*, so that at least one timestamp of the event must correspond to one of the days in the week after using strtotime().

array(7) { [0]=> string(10) "18-09-2023" [1]=> string(10) "19-09-2023" [2]=> string(10) "20-09-2023" [3]=> string(10) "21-09-2023" [4]=> string(10) "22-09-2023" [5]=> string(10) "23-09-2023" [6]=> string(10) "24-09-2023" }

the same array with timestamp : array(7) { [0]=> string(10) "1694988000" [1]=> string(10) "1695074400" [2]=> string(10) "1695160800" [3]=> string(10) "1695247200" [4]=> string(10) "1695333600" [5]=> string(10) "1695420000" [6]=> string(10) "1695506400" }

Must I repeat array('key' for each date of event period range ? If yes can I do a loop from array of dates event ?

My query:

$ActivitiesType = array(
    'post_type' => $activityType,
    'post_status' => 'publish',
    'posts_per_page'   => -1,
    //'meta_key' =>'start_timestampevents',
    'orderby' => 'meta_value_num',
    'order' => 'ASC',

    'meta_query' => array(
        'relation' => 'OR',
            array(
                'key' => 'end_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            ),
            array(
                'key' => 'start_timestampevents',
                'value' => array($firstdayweek_ts,$lastdaymidnight_ts),
                'compare' => 'BETWEEN',
                'type' => 'NUMERIC'
            )
    )       
);

Answer 1

To be able to do this your post meta values need to be strings in a valid date format, e.g. 2022/01/31 for January 31st 2022. Note I used ISO standard timestamp format with the biggest value first, then everything descending, and avoided using a regional value such as 31/1/2022 from the UK, or 1/31/2023 from the USA.

Unfortunately though, none of your values fit this format, making your task impossible until the data is stored in the correct way. Your data is not a timestamp string, it's a string of a serialised PHP array that just happens to contain timestamps. MySQL has no idea there is an array in the meta value column, it sees a string, not a PHP data structure. This is why you can't sort/order/filter by it or use clauses that depend on logic such as BETWEEN >= etc. The most you can do is search the string or use a regular expression, both of which can't be used for this task.

Fixing The Data

Instead you need to do several things to fix this:

• stop using structure data! It's very likely you're passing an array to update_post_meta/add_post_meta, or using a metabox/fields library that does this behind the scenes. That needs to stop immediatley
• store the individual timestamps as individual separate meta. Post meta keys are not unique, instead of storing a single start_timestampevents that holds lots of data, store multiple start_timestampevents.
• This is why we have add_post_meta, why delete_post_meta takes a value, and why get_post_meta has a 3rd parameter ( it's usually set to true to return only one result, but if you set it to false it will return an array of results instead! )

You will also need to convert all your old data to the new system. A custom WP CLI commands will help here, however it may be easier to change the name of your meta key, then fetch 20 posts that have the old key and convert them on the admin_init hook followed by deleting them so it doesn't process the same posts again. Once that loop stops finding posts it will have finished migrating and you can remove that code, with the benefit being that by running on admin_init and only doing 50 at a time you're not killing your site or ruining visitors page performance.

Once the data is fixed, you can set your meta query data type to date and time as specified in WP_Query's dev docs, and all the examples from that page should work and apply to your use case. The problems you mentioned about trying to specify keys should vanish.