Skip to toolbar

Using Date based Searches

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.

Overview

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 < and > 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.

Between Examples

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.