How to filter by date

[ahmedo]

Hello,

I have a list of subscribers in my table. I have a field called purchase_date (date type).
I want to filter my records to show only those whose purchase_date is less than 365 days. How to go about doing this.

Thank you.

 

[troester]

Do you want do filter manually (i.e. select the date range in list view)?
Set the field to Filter type = range in "List view settings"/Filters

Or do you want to show the list pre-filtered (pre-filters are not removable by "Clear filter")?
Add a prefilter in list settings (Data/Pre-filter) or in menu settings (Fabrik list options /Pre-filters)

 

[ahmedo]

Thanks for the reply. I wanted to add a menu item with the pre-filtered option that shows those that their purchase date is more than 365 days. Steve has answered, but he cannot post here. I asked his permission to post his answer in this section. Once he says Ok, I will do it. Thanks again.

 

[ahmedo]

Steve has been generous enough to allow me to share his answer which worked like a charm. Below is his answer.

I am using a pre-filter to display events 3 days earlier than the current date and for the rest of the current year with this;
Element: START DATE
Condition: GREATER THAN OR EQUAL you may need to change this selection from the Condition dropdown box to achieve what you require
Value: DATE_SUB(CURDATE(),INTERVAL 3 DAY)
Type: No Quotes
Access: Public
The 3 can be any number of days you want it to be
Hope it helps
Steve

 

[troester]

Steve is @stevelis ?

 

[ahmedo]

Steve is @stevelis ?

Yes. There is a link on his name.

 

[cheesegrits]

Note that by doing it "manually' like that (using DATE_SUB(CURDATE()) you may run in to timezone issues, depending how your site is set up. You might want to test with a date which is within your TZ offset of being in range.

Remember that by default, Fabrik stores dates in UTC (unless you've set "Store as Local"). And CURDATE() may return UTC or your local TZ (or some entirely unrelated TZ), depending how MySQL is configured.

So when dealing directly with dates like that, you may need to use CONVERT_TZ() ...

DATE_SUB(CONVERT_TZ((CURDATE(), '+00:00', '+05:00'),INTERVAL 3 DAY)

More often than not CURDATE() will return UTC, but it's not guarranteed. So if it's important to your application that you don't either include dates you shouldn't, or miss out dates, you need to test. So if your TZ is (say) GMT-2, test with two dates that are within 2 hours of midnight either side, 3 days ago, and see which one(s) show up. That'll tell you which way you need to shift the TZ.

-- hugh

 

[ahmedo]

Thank you for the answer. At the moment, few hours will make not difference to our need. In fact we give 90 days buffer to our customers before we consider their subscription expires.


Sent from my iPhone using Tapatalk

 

[cheesegrits]

Subscriptions?

That's a keyword for me at the moment - I'm working on a Stripe subscription plugin, for handling recurring payments through the Stripe payment processor. is that something you might be interested in?

-- hugh

 

[ahmedo]

Hi Hugh,

Not at the moment. We have full-time software developers who are developing Stripe payment on a different tool. The one I am using Fabrik is for internal consumption. It is not a public site.