Date Time picker on a table in perspective

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?

  • 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

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

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

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?

I did figure this out thank you

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!

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.

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!

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