Filter today's events in dataset

Get Help
1

Hi,

Finally got the advanced filter to work for showing only today’s events from a dataset. I used the following query where Starts is the event starting date.
DATE_FORMAT(NOW(), ‘%Y-%m-%d 00:00:01’) < Starts AND Starts < DATE_FORMAT(NOW(), ‘%Y-%m-%d 23:59:59’)

May help someone.

CMS v1.8.7

2

Another variation of this which might be a little more simple:
DATE(Start) = DATE(NOW())
The Date function in MySQL converts a timestamp date (i.e. ‘2018-06-10 08:00:00’) to just the date value.

I use a similar function for my event datasets (which have both a Start and Finish time column).
The following means the event data will only display between the times specified:
NOW() BETWEEN 'Start' AND 'Finish'.

If you want to take this further, then you can use the MYSQL Interval function to add or subtract hours from the Start column. This allows you to display the event on your layout before it starts. The example below starts the event displaying 1 hour before the event actually starts.

NOW() BETWEEN DATE_SUB('Start', INTERVAL 1 HOUR) AND 'Finish'

If you’re dataset has another column, let’s same for the Meeting Room, the you can add this to your code as well:
'ROOM' IN ('Room Name') AND NOW() BETWEEN DATE_SUB('Start', INTERVAL 1 HOUR) AND 'Finish'

Hope this helps!

3

Thanks justinh. That made it pretty simple.

The MYSQL Interval function will also be very useful specially when used with IN. Should meet our requirement for event room displays.

1 Like