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

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.