# Query Database based on date instead of t\_stamp in Ignition Tag Historian database

**URL:** <https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894>\
**Category:** Ignition\
**Created:** [April 25, 2023, 11:16pm UTC](https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894 "2023-04-25T23:16:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Babla\_Bogam](https://avatars.discourse-cdn.com/v4/letter/b/ecb155/32.png) [@Babla\_Bogam](https://forum.inductiveautomation.com/u/Babla_Bogam)\
**Post date:** [April 25, 2023, 11:16pm UTC](https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894/1 "2023-04-25T23:16:49Z")

</div>

Hello, I wanted to write a sql query (Microsoft Sql Server) against the Ignition Tag Historian database and Table sqlt\_data\_X\_2023\_XX. The table has the tag values and t\_stamp (which is milliseconds). How can I query based on date instead of the t\_stamp?

---

<div class="post-metadata">

**Author:** ![Babla\_Bogam](https://avatars.discourse-cdn.com/v4/letter/b/ecb155/32.png) [@Babla\_Bogam](https://forum.inductiveautomation.com/u/Babla_Bogam)\
**Post date:** [April 26, 2023, 12:24am UTC](https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894/2 "2023-04-26T00:24:52Z")

</div>

I found the solution that worked for me. I used the CONVERT function - CONVERT(datetime, DATEADD(second, t\_stamp/1000, '19700101'))

---

<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:** [April 26, 2023, 8:09am UTC](https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894/3 "2023-04-26T08:09:42Z")

</div>

Just make sure that your search is searching in milliseconds and not converting each t\_stamp to a date as this would slow down the query a lot.

```auto
SELECT t_stamp, tag
FROM tablename
WHERE convertToDate(t_stamp) >= someDate

```

The above query has to convert every t\_stamp before the compare.

A better way is to compare t\_stamp with the millisecond value as this only has to do one conversion.

```auto
SELECT t_stamp, tag
FROM tablename
WHERE t_stamp >= toMIllisecond(someDateValue)

```

(The queries above are very rough pseudo-code just to give the idea.)

---

<div class="post-metadata">

**Author:** ![Babla\_Bogam](https://avatars.discourse-cdn.com/v4/letter/b/ecb155/32.png) [@Babla\_Bogam](https://forum.inductiveautomation.com/u/Babla_Bogam)\
**Post date:** [April 27, 2023, 4:12pm UTC](https://forum.inductiveautomation.com/t/query-database-based-on-date-instead-of-t-stamp-in-ignition-tag-historian-database/72894/4 "2023-04-27T16:12:05Z")

</div>

Thank you