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