Convert Unix time from IPFix to datetime in SQL

Asked Sep 15 '14 at 22:47

Active Sep 18 '14 at 21:43

Viewed 102 times

I have an IPFix timestamp which appears to be in Unix time. My SQL code is:

update 
  temp_ipfix 
set FlowStartTimeDT = dateadd(S, FlowStartTime, '1970-01-01 00:00:00')

Where FlowStartTime is the column containing the Unix timestamp.

A sample timestamp is 1410435197783

The error I'm getting is:

Arithmetic overflow error for type int, value = 1410435197783.000000. The statement has been terminated

I tried the suggestions linked to this page, but the results are incorrect. Month and Day are correct, but year is 2034.

select dateadd(s, (flowstoptime/1000), '1990-01-01 00:00:00') from temp_unix

edited Sep 18 '14 at 21:43

asked Sep 15 '14 at 22:47

user3638757

please put the updates in your question and not the comment box\ – CMPS Sep 15 '14 at 22:49
What platform are you using? SQL Server? – crthompson Sep 15 '14 at 22:52
Yes. SQL Server 2014 – user3638757 Sep 15 '14 at 23:04
Using [this answer](http://stackoverflow.com/a/19979905/2589202) you can get around the limits. Check out [my fiddle](http://sqlfiddle.com/#!6/51391/8) – crthompson Sep 15 '14 at 23:30

0 Answers0