Historical t_stamp

How is the t_stamp column bigint) in the historical table converted to a date/time

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]”

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)

Like Carl said, it’s the number of milliseconds since midnight of January 1st, 1970 … UTC/GMT … in T-SQL, you can use …

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

I’ve used 05:00:00:0000 since my location (Pennsylvania) is 5 hours behind@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 to get the result in a form that works for you.

… or you could just cheat instead :smiley:

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