# MySQL Table FIltering **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective, database\ **Created:** [June 8, 2023, 8:07pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796 "2023-06-08T20:07:56Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\ **Post date:** [June 8, 2023, 8:07pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/1 "2023-06-08T20:07:56Z")
Hi I have an audit schema named events in MySql I have created a audit profile named Events The table name for the audit is AUDIT\_EVENTS I need an Audit Log Display in perspective filtered on ACTOR - (ex. 'usr-prov:BMS\_USERS:/usr:JZunigaEspitia') Display needs to allow the operator to set beginning and ending dates However nothing seems to be getting logged to the AUDIT\_EVENTS table I don`t know how to create the date filters check screenshots attached. Could you help me to create it? ![Screenshot 2023-06-08 155640](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/c/ccdec1996163fc12f5364d3baa49f966fe44e997.png) ![MicrosoftTeams-image (5)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/7/3767fe0564888069c91f31a4741d420333605b3f.png) ![MicrosoftTeams-image (4)](https://us1.discourse-cdn.com/inductiveautomation/original/3X/5/3/5366859be6db5db6eee22132ef0168567b8be7be.jpeg) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [June 8, 2023, 8:17pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/2 "2023-06-08T20:17:52Z")
You're last screenshot is correct as far as the query string goes. Change the parameter data types to `Datetime` instead of `String`. Next when you bind the named query to your table, use date picker components, then use their values as the source for the query parameters. ---
**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\ **Post date:** [June 8, 2023, 8:21pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/3 "2023-06-08T20:21:30Z")
Thank you but sorry what is a date picker components. Could you give me a sample? ---
**Author:** ![Transistor](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/transistor/32/3380_2.png) [@Transistor](https://forum.inductiveautomation.com/u/Transistor)\ **Post date:** [June 8, 2023, 8:27pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/4 "2023-06-08T20:27:13Z")
It's actually the DateTime Picker component. It can be found in the Perspective Components panel (usually on the right side of the screen) in the Inputs section. [https://docs.inductiveautomation.com/display/DOC81/Perspective+-+DateTime+Picker](https://docs.inductiveautomation.com/display/DOC81/Perspective+-+DateTime+Picker) ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [June 8, 2023, 8:28pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/5 "2023-06-08T20:28:01Z")
I highly recommend going through [Inductive University](https://inductiveuniversity.com/) for introduction training for free! On the right side of the designer, inside the Perspective Components panel, has all the pre-built perspective components inside. You can drag and drop these components into your view. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/8/9866e271ebe0059511763c07d65696d34cad93d6.png) You showed your table in a screenshot already. On it's data property, bind it to the date picker value. Click on the little `fx` to get your component path for the binding. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/a/ca9905d34500a0d1d177ebc81a49932988cac127.png) ---
**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\ **Post date:** [June 9, 2023, 12:38pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/6 "2023-06-09T12:38:15Z")
Hi, I got it and its very clear thank you. But something still went wrong. That s te message I`ve got. ![fault1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/c/3cccd29736cf22556cacb454beda0769c13216ab.png) ![Fault2](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/8/684dce1ef6c732682b0d4ca2434b589f6fd1fd0d.png) As you can see The sellection doesn`t works. ![Fault3](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/5/353f5cb8d3e9d3f1ff88760e8dfc13de6d500def.png) This is what I have in the page ![Fault4](https://us1.discourse-cdn.com/inductiveautomation/original/3X/7/7/778dbe7020245bce8eb998ca2b9bcae2a895f820.png) ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 9, 2023, 12:40pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/7 "2023-06-09T12:40:37Z")
Parameters should be like `:date1` and `:date2` with a leading colon. MySQL is currently interpreting it as you are referencing two columns `date1` and `date2` which don't exist in the table hence the error `Invalid column name: 'date1'` ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [June 9, 2023, 12:41pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/8 "2023-06-09T12:41:03Z")
When you use parameters in the query, you have to call them out with a colon. `:date1` and `:date2` @bkarabinchak.psi beat me to it 🙂 ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 9, 2023, 12:42pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/9 "2023-06-09T12:42:05Z")
I had my coffee this morning before coming on today 🙂 ---
**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\ **Post date:** [June 9, 2023, 12:49pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/10 "2023-06-09T12:49:50Z")
Amazing Thank you guys the fault is gone... ![Screenshot1](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/d/cd1cc01101fb134e87162dec085ddd312b215eae.png) Last question, is it possible to use same DateTime Picker object to date1 and date2? ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [June 9, 2023, 12:56pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/11 "2023-06-09T12:56:20Z")
You could but if your query ends up like `BETWEEN '2023-06-08 08:55:00' AND '2023-06-08 08:55:00'` where the dates are identical you're never going to get any records. However if you have the user pick one date, and have a custom property that calculates the other date, and use those two - that would work. ---
**Author:** ![dkhayes117](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/dkhayes117/32/68344_2.png) [@dkhayes117](https://forum.inductiveautomation.com/u/dkhayes117)\ **Post date:** [June 9, 2023, 1:20pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/12 "2023-06-09T13:20:47Z")
There is an [open ticket](https://ideas.inductiveautomation.com/ignition-features-and-ideas/p/perspective-date-picker-that-allows-selecting-date-range) to allow a date picker to select a range, but it could be a while before it is implemented. ---
**Author:** ![Leandro\_Garcia\_Carva](https://avatars.discourse-cdn.com/v4/letter/l/6f9a4e/32.png) [@Leandro\_Garcia\_Carva](https://forum.inductiveautomation.com/u/Leandro_Garcia_Carva)\ **Post date:** [June 9, 2023, 2:33pm UTC](https://forum.inductiveautomation.com/t/mysql-table-filtering/74796/13 "2023-06-09T14:33:41Z")
Hi guys! Thank you very much for all your help and patience. It's working perfectly. Regards, Leandro