Filtering Date Fields using "before" and "after"

LiveWhale
1

Hello,

In brief:

  • I have a profile that has a “date” field.
  • I have a widget that lists those profiles.
  • I want that widget to only list items with that “date” field between today and 15 months ago.

I’ve been trying everything I can think of, including PHP-readable strings and possibilities based on this SQL value I found in our other widgets:

<arg id="filter" name="date2_dt" action="after">NOW()</arg>
<arg id="filter" name="date_dt" action="before">NOW()</arg>

No matter what I try, the intended result doesn’t appear. Seeing the above widget arguments, I suspect that I can’t do a time-based comparison on a “date” field.

I made a quick profile type and widget to test this, and this appears to be the case.

No filter. (Control)

Filter test: no filter, all three results appear.
Filter test: no filter, all three results appear.1168×648 24.9 KB

Before date. (Fail)

Filter test: date before now, all results appear. (Fail)
Filter test: date before now, all results appear. (Fail)1235×690 29.5 KB

After date. (Fail)

Filter test: date after now, no results appear. (Fail)
Filter test: date after now, no results appear. (Fail)1184×693 29.5 KB

Before datetime. (Success)

Filter test: datetime before now, past and today appear. (Success)
Filter test: datetime before now, past and today appear. (Success)1192×691 27.9 KB

After datetime. (Success)

Filter test: datetime after now, future appears. (Success)
Filter test: datetime after now, future appears. (Success)1185×689 27.4 KB

Thus…

  • Is there a way I can achieve this kind of relative comparison on a date field, without making it datetime?
  • If not, could this behavior be considered for the LiveWhale roadmap and future versions?
    • I imagine a solution like, “if filter before/after date, append midnight as the time and do the existing datetime compare”.

Any input and direction is welcome.

Thanks,
Nick

2

Ah, an alternative idea that would be more useful for my current need.

Is there a way to define the {expiration_date} and {expiration_time} (and “Expires on…”) of a profile when it is made using $_LW->create()? If so, what is the expected format?

In brief, I have an accomplishment submission form that, on success, creates a profile. Given we want these profiles to hide themselves 15 months after they’re first relevant (that date field), I could determine the expiration date in the custom module ( date + 15 months ) then potentially assign that value to the profile.

Would be somewhat better than just limiting the time range of the widget, given it would help keep the dashboard list of profiles clean also.

Update: Yes, you can define the expiration!

$data_to_save['expiration_date'] = date('m/d/Y', $expire_timestamp);
$data_to_save['expiration_time'] = date('g:ia', $expire_timestamp);

Well, that solves my immediate issue, but the thread’s main issue persists.

3

Another use case has appeared:

Would like the widgets to display courses that haven’t yet occurred (custom end date > now).

Thanks,
Nick

4

Thanks Nick – noted on the desire to + usefulness of being able to do date comparisons on custom datetime fields.

I wonder if some onWidgetFormat - LiveWhale Support might offer a band-aid solution in the meantime, if you could do some processing on your custom_421 to check it against a PHP logic of current date/time and then either add some class=“hidden” or such to the onWidgetFormat buffer variables that gets passed through to the results? Hope this may help!