Date Time picker on a table in perspective

Ignition
1

I have a table component bound to a named query in perspective. How do I integrate a date time picker on the timestamp in the table to allow the operator to filter the table with this component?

image
image770×855 36.4 KB

2
  • Modify the named query to accept two parameters.
WHERE t_stamp >= :startDate
  AND t_stamp <= :endDate

(Or use the BETWEEN syntax if your DB supports it.)

  • Add two date pickers to the view. Bind each to a view custom property filterStartDate and filterEndDate. (You could, instead, bind them to session variables if the dates will be used by more than one view.)
  • In the table binding you should now see the two empty variables listed in the query binding. Bind each of these to the relevant custom property or session variable created above.

Now any time you change the date range the query will be executed again.

1 Like
3

That should be

  AND t_stamp < :endDate

With endDate containing the proper start of the next period. Half-open intervals prevent losing records that fall in the last minute of a period (when the endDate specifies the last minute) or the last second of a period (when the endDate specifies the last second, but t_stamp carries millisecond precision).

3 Likes
4

Can you give an example of the custom property binding? Currently I have an error on the table when trying to bind the date time pickers to the variables startDate and endDate in the named query. Thanks

5

image
image1920×1040 159 KB

6

Technical note: The binding is the other way around. You bind the query parameters to the variables.

Can you post a (nicely cropped) screen grab of the query binding so we can see what's bound to what?

7

I did figure this out thank you

8

Hello, I have a similar problem, I bind the tag to the date picker but the picker only allow me to select one date but not the end date. Could you kindly explain how to solve this? Also my timestamp is in milliseconds, if that's the cause how should I change the format to match datetime picker? Thank you!

image
image1228×762 74.4 KB

9

picker only allow me to select one date but not the end date.

You need two date pickers. One for the start and one for the end.
The alternative is to add a fixed offset such as one hour or one day. You can do this by putting an expression in the End Date field.

Also my timestamp is in milliseconds, if that's the cause how should I change the format to match datetime picker?

Ignition will understand that automatically. You don't have to do anything. DatePicker will return data type of datetime and that's what Ignition's Historian uses.

10

Thank you! Selecting times now work perfectly, however, the timastamp returns to the table is still in milliseconds, could you advise how to convert this as same as time picker format? 2023-9-26 0:00:00

Thanks!

image
image1036×767 40.7 KB

11

You need to tell the table which field to render in each column and how they are to be rendered. Set the following properties:

columns.0.field : t_stamp
columns.0.render : date
columns.0.dateFormat : YYYY-MM-DD HH:mm:ss (or whatever)
columns.0.header.title : Timestamp (optional)

columns.1.field : chopper_in_use
colums.1.render : auto (or whatever)
columns.1.header.title : Chopper in use
12

i need help. how to use this time picker for XY chart? same configure or what?

13

Fadillah, this thread is about Time Picker for use with the Perspective Table component. Please start a new thread.