# Date Time picker on a table in perspective

**URL:** <https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010>\
**Category:** Ignition\
**Tags:** perspective\
**Created:** [June 14, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010 "2023-06-14T11:48:13Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![joseph.hahn](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@joseph.hahn](https://forum.inductiveautomation.com/u/joseph.hahn)\
**Post date:** [June 14, 2023, 11:48am UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/1 "2023-06-14T11:48:13Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/1/8/189e314624d650dd8dfd50fe2b1aace793f5edee.png)

---

<div class="post-metadata">

**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 14, 2023, 12:07pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/2 "2023-06-14T12:07:17Z")

</div>

- Modify the named query to accept two parameters.

```auto
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.

---

<div class="post-metadata">

**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\
**Post date:** [June 14, 2023, 12:38pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/3 "2023-06-14T12:38:37Z")

</div>

> [@Transistor](#):
>
> ```auto
> AND t_stamp <= :endDate
> 
> ```

That should be

```auto
  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).

---

<div class="post-metadata">

**Author:** ![joseph.hahn](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@joseph.hahn](https://forum.inductiveautomation.com/u/joseph.hahn)\
**Post date:** [June 14, 2023, 1:18pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/4 "2023-06-14T13:18:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![joseph.hahn](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@joseph.hahn](https://forum.inductiveautomation.com/u/joseph.hahn)\
**Post date:** [June 14, 2023, 1:19pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/5 "2023-06-14T13:19:13Z")

</div>

![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/d/c/dcb0e6e4548d985b9bf395b9a99d3e8d0ec79ecc.png)

---

<div class="post-metadata">

**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 14, 2023, 2:19pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/6 "2023-06-14T14:19:25Z")

</div>

> [@joseph.hahn](#):
>
> 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.

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?

---

<div class="post-metadata">

**Author:** ![joseph.hahn](https://avatars.discourse-cdn.com/v4/letter/j/4da419/32.png) [@joseph.hahn](https://forum.inductiveautomation.com/u/joseph.hahn)\
**Post date:** [June 14, 2023, 3:21pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/7 "2023-06-14T15:21:30Z")

</div>

I did figure this out thank you

---

<div class="post-metadata">

**Author:** ![Wenhe\_Zhao](https://avatars.discourse-cdn.com/v4/letter/w/4da419/32.png) [@Wenhe\_Zhao](https://forum.inductiveautomation.com/u/Wenhe_Zhao)\
**Post date:** [October 10, 2023, 1:16pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/8 "2023-10-10T13:16:16Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cbfa88ae768e8edad63e199039368703914354d8.png)

---

<div class="post-metadata">

**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:** [October 10, 2023, 2:06pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/9 "2023-10-10T14:06:20Z")

</div>

> 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.

---

<div class="post-metadata">

**Author:** ![Wenhe\_Zhao](https://avatars.discourse-cdn.com/v4/letter/w/4da419/32.png) [@Wenhe\_Zhao](https://forum.inductiveautomation.com/u/Wenhe_Zhao)\
**Post date:** [October 10, 2023, 5:40pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/10 "2023-10-10T17:40:50Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/5/65b26d9da808beb29aee91dbbe8245eaca6c87a0.png)

---

<div class="post-metadata">

**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:** [October 10, 2023, 6:30pm UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/11 "2023-10-10T18:30:26Z")

</div>

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

```auto
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

```

---

<div class="post-metadata">

**Author:** ![Fadillah\_Ariadinata](https://avatars.discourse-cdn.com/v4/letter/f/4af34b/32.png) [@Fadillah\_Ariadinata](https://forum.inductiveautomation.com/u/Fadillah_Ariadinata)\
**Post date:** [June 25, 2024, 6:09am UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/12 "2024-06-25T06:09:42Z")

</div>

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

---

<div class="post-metadata">

**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 25, 2024, 10:49am UTC](https://forum.inductiveautomation.com/t/date-time-picker-on-a-table-in-perspective/75010/13 "2024-06-25T10:49:09Z")

</div>

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