# Convert Epoch Time in SQL Query **URL:** \ **Category:** Ignition\ **Tags:** perspective\ **Created:** [April 10, 2024, 3:04pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644 "2024-04-10T15:04:11Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:04pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/1 "2024-04-10T15:04:11Z")
Hello everyone, I'm trying to find the format for converting from epoch time to normal time. We have a PLC logging timestamps in epoch time and I can't get it working. I need to pull the last 3 minutes of data from this table and average the weight for our products but can't find a format thats working when trying to setup a named query. Can anyone point me to some documentation that should work for this? ---
**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 10, 2024, 3:06pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/2 "2024-04-10T15:06:37Z")
Show us a sample of your data and your SQL query. Format them properly with the \ button. See [Wiki - how to post code on this forum](https://forum.inductiveautomation.com/t/wiki-how-to-post-code-on-this-forum/77831) if unsure how. ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:12pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/3 "2024-04-10T15:12:53Z")
Here is my query I was looking at based on another post and some information i found online for converting epoch time. ```auto SELECT AVG(floatvalue) as avg_floatvalue, to_timestamp(t_stamp) as converted_t_stamp FROM sqlt_data_3_2024_04 WHERE tagid = 161 AND to_timestamp(t_stamp) >= DATEADD(MINUTE, -3, GETDATE()) ``` Here is a screen show of the table im pulling from. I need to average the float. ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/b/fba651e075561d51696153eaa273004863eca2b1.png) ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [April 10, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/4 "2024-04-10T15:14:40Z")
For MSSQL: ```auto DATEADD(s, t_stamp/1000, '1970-01-01') ``` ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:14pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/5 "2024-04-10T15:14:57Z")
ill try it out ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 10, 2024, 3:18pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/6 "2024-04-10T15:18:16Z")
Why are you manually querying the Tag History tables, rather than using the built in functions? Your query will fail (silently) if your 3 min sample crosses partition boundries. ---
**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:** [April 10, 2024, 3:18pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/7 "2024-04-10T15:18:16Z")
> [@NP209](#): > > `FROM sqlt_data_3_2024_04 ` You are running direct queries against Ignition's historian tables. This is extremely unwise (and unmaintainable). Use Ignition native historian access methods. ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:18pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/8 "2024-04-10T15:18:45Z")
Matrix I think you saved me a headache, its not throwing an error, I just need to check to verify the math is working properly! ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:19pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/9 "2024-04-10T15:19:29Z")
oh, I will look into it, I didn't realize it was not a good idea. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 10, 2024, 3:20pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/10 "2024-04-10T15:20:43Z")
> **[system.tag.queryTagHistory | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/appendix/scripting-functions/system-tag/system-tag-queryTagHistory)** > > Issues a query to the Tag Historian. Or for a binding, use a Tag History Binding ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:20pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/11 "2024-04-10T15:20:50Z")
I'm not familiar with that as I'm learning Ignition on the fly for my work. Is there some documentation that you are aware of you can point me to to provide some depth on it? If not I appreciate the heads up! ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:23pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/12 "2024-04-10T15:23:54Z")
I took over this project but before me, the company I work for has a PLC writing directly to the Table I'm trying to setup a query Tag for. Is this Bad? Sounds like I should be having the data flow through to ignition into a tag and enabling the history for it and using the system.tag.queryTagHistory feature? ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 10, 2024, 3:34pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/13 "2024-04-10T15:34:59Z")
> [@NP209](#): > > I took over this project but before me, the company I work for has a PLC writing directly to the Table I'm trying to setup a query Tag for. Is this Bad? It appears to me, based on the query that you showed, that by "PLC writing directly to the Table" what you're actually seeing is the result of an already existing OPC tag with History enabled. I avoid query tags at all costs, they have there uses, but they are not a great solution for many things that they can be used for. From the information you have provided, the correct way to collect the data you are interested in, is to use the pre-built in Tag History functions, weather that be with a binding or a script. What is the real problem you are trying to solve? What are planning to do with this 3 minute sample of data once you get it back? Where are you trying to get this data from and to? ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:41pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/14 "2024-04-10T15:41:35Z")
We have a simple dashboard that displays averages for certain things. In this case getting the average weight factors into calculating the how much giveaway we have in our product. ---
**Author:** ![lrose](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lrose/32/62724_2.png) [@lrose](https://forum.inductiveautomation.com/u/lrose)\ **Post date:** [April 10, 2024, 3:47pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/15 "2024-04-10T15:47:10Z")
And this average needs to be displayed in what? A label? How is the time span selected is it always constant or does the user select? I'll assume a Label, look into a Tag History Binding. > **[Tag History Bindings in Perspective | Ignition User Manual](https://docs.inductiveautomation.com/docs/8.1/ignition-modules/perspective/working-with-perspective-components/bindings-in-perspective/tag-history-bindings-in-perspective#tag-history-binding)** > > Tag History Binding ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:48pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/16 "2024-04-10T15:48:14Z")
There isn't any user interaction really. Simply informational pretty much. If something is off or looks way wrong its a visual que to stop and adjust or inspect whats happening. ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:48pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/17 "2024-04-10T15:48:54Z")
Its going to be displayed on a Label. It will be a constant value displayed when that production line is being run, which isnt always the case. ---
**Author:** ![NP209](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/np209/32/80704_2.png) [@NP209](https://forum.inductiveautomation.com/u/NP209)\ **Post date:** [April 10, 2024, 3:57pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/18 "2024-04-10T15:57:21Z")
The TagHistory Binding requires a tag to be used right? I dont have a tag to use with that feature. All I have is a table to pull from. ---
**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:** [April 10, 2024, 3:59pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/19 "2024-04-10T15:59:46Z")
You will want a tag history binding that uses return size = 1, for a time span from `now()` minus three minute to `now()`. And "average" aggregation mode. You'll get one row in the result, from which an expression binding can extract the one value. ---
**Author:** ![pascal.fragnoud](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pascal.fragnoud/32/24728_2.png) [@pascal.fragnoud](https://forum.inductiveautomation.com/u/pascal.fragnoud)\ **Post date:** [April 10, 2024, 3:59pm UTC](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644/20 "2024-04-10T15:59:49Z")
The table you showed is part of the `tag historian`. It seems unlikely that you don't have a corresponding tag. [Next page](https://forum.inductiveautomation.com/t/convert-epoch-time-in-sql-query/88644.md?page=2)