# Historical t\_stamp

**URL:** <https://forum.inductiveautomation.com/t/historical-t-stamp/1734>\
**Category:** Ignition\
**Created:** [April 7, 2010, 5:44pm UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734 "2010-04-07T17:44:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![eliteaut](https://avatars.discourse-cdn.com/v4/letter/e/ecae2f/32.png) [@eliteaut](https://forum.inductiveautomation.com/u/eliteaut)\
**Post date:** [April 7, 2010, 5:44pm UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734/1 "2010-04-07T17:44:49Z")

</div>

How is the t\_stamp column bigint) in the historical table converted to a date/time

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 7, 2010, 8:11pm UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734/2 "2010-04-07T20:11:53Z")

</div>

It is the number of milliseconds since “the epoch” - Jan 1st 1970. If you’re using MySQL, you can convert this to a datetime using “[tt]from\_unixtime(t\_stamp/1000)[/tt]”

---

<div class="post-metadata">

**Author:** ![Kevin.McClusky](https://avatars.discourse-cdn.com/v4/letter/k/47e85d/32.png) [@Kevin.McClusky](https://forum.inductiveautomation.com/u/Kevin.McClusky)\
**Post date:** [April 8, 2010, 12:25am UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734/3 "2010-04-08T00:25:59Z")

</div>

I just had to do this with SQL Server. The query I used:

SELECT dateadd(hour, -7, dateadd(second, [t\_stamp]/1000, ‘1970-01-01’))

(I’m not sure why I needed to subtract 7 hours)

---

<div class="post-metadata">

**Author:** ![bryan\_cook](https://avatars.discourse-cdn.com/v4/letter/b/a8b319/32.png) [@bryan\_cook](https://forum.inductiveautomation.com/u/bryan_cook)\
**Post date:** [April 8, 2010, 1:16am UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734/4 "2010-04-08T01:16:41Z")

</div>

Like Carl said, it’s the number of milliseconds since midnight of January 1st, 1970 … [UTC](http://en.wikipedia.org/wiki/UTC)/[GMT](http://en.wikipedia.org/wiki/GMT) … in T-SQL, you can use …

```auto
SELECT DATEDIFF(s, '19700101 05:00:00:000', GETUTCDATE())
```

I’ve used 05:00:00:0000 since my location (Pennsylvania) is [5 hours behind](http://upload.wikimedia.org/wikipedia/commons/e/e7/Timezones2008.png) … @Kevin.McClusky, I’m sure your -7 had something to do with your location … do you live in the Midwest/West?

Additionally, with the statement above, you can replace the s with [a number of format strings](http://msdn.microsoft.com/en-us/library/ms189794.aspx) to get the result in a form that works for you.

… or [you could just cheat](http://www.epochconverter.com/) instead 😃

---

<div class="post-metadata">

**Author:** ![Carl.Gould](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/carl.gould/32/7262_2.png) [@Carl.Gould](https://forum.inductiveautomation.com/u/Carl.Gould)\
**Post date:** [April 8, 2010, 2:32am UTC](https://forum.inductiveautomation.com/t/historical-t-stamp/1734/5 "2010-04-08T02:32:25Z")

</div>

> [@bryan\_cook](#):
>
> ... or [you could just cheat](http://www.epochconverter.com/) instead 😃

Nice site! I'll file this one away...

In computers, there is no cheating. The quicker you solve problem A (by any means), the quicker you can get to problem B...