Date Based Math can get very confusing, especially since dates are not consistent between different storage mechanisms and also between US and European dates. We try to break down some of the confusion when doing Date Based Searches by showing how dates are stored, how to put them in the same format and how to do simple commonly used date math.
Date Storage in Custom Fields
One thing to be aware of when dealing with date fields in Pods (and WordPress in general) is that the only date field that is actually stored in the database as a date is the
post_date; Date fields stored in Custom meta (
wp_postmeta) are actually stored in the format
yyyy-mm-dd; Date/Time Fields are stored as
yyyy-mm-dd hh:mm:ss in 24 hour time format. This allows the values to be sorted alphanumerically and still work properly.
Upcoming Date & BETWEEN
Upcoming is date-based math, so you’ve got to use a
where clause in your shortcode. Since
> get stripped out naturally by WP, the best method for this is the
between SQL operator.
If you’re using post_date, you’re in luck, because it’s already a ‘date’; if you’re using a date field instead, it’s actually stored as text in wp_postmeta, which means
yyyy-mm-dd hh:mm:ss format. You can flip the field to be treated as a date very simply by using the
cast operator. So basically, assuming you have an
event_date as the date we’re comparing:
where="CAST(event_date.meta_value as DATE) BETWEEN CURDATE() and '2020/01/01'"
We recommend using the beginning of the next year as the end date of the ‘between’ test because it’s rare you need to show more than a year into the future.
CURDATE() is the MySQL command for the current date.
If you only want to show the next event and no more, add a
limit=1 parameter to the above shortcode to only show the next entry.
For a specific single date? We’ll want to use something like this (Thursday 17 January 2020)
where="CAST(date_of_event.meta_value as DATE) BETWEEN '2020/01/17' and '2020/01/17'"
Friday 18 January 2020
where="CAST(date_of_event.meta_value as DATE) BETWEEN '2020/01/19' and '2020/01/18'"
Saturday 19 January 2020
where="CAST(date_of_event.meta_value as DATE) BETWEEN '2020/01/19' and '2020/01/19'"
Be sure your dates are formatted correctly in your shortcode(s) and are matching what you have set in your Pods’ CPT field.