# How do i calculate unix time from sql datetime

I have a table of eventsthat has its timestamp for each row as data type datetime2(3) - ie. date and time with millisecond precision.  An example timestamp would be 2011-01-02 13:31:43.567.

I need to calculate the Unix timestamp in a query, given that timestamp column.  Does anyone have a simple way of doing this?  I need the same precision, ie. millisecond precision.  Unix time is calculated as a number of elapsed seconds (and milliseconds of course) since 1970-01-01 00:00:00.000 at GMT.  So, in this example, the Unix time would be 1293975103.567.

###### Who is Participating?

progCommented:
your code gives an overflow in 2040 - the unix Y2K + 39 problem

DECLARE @TIMESTAMP as datetime ;
SET @TIMESTAMP = '2039-01-02 13:31:43.567';
--SET @TIMESTAMP = '2011-01-02 13:31:43.567';

--adjust for GMT??  in the test code but not the example given
--SET @TIMESTAMP = DATEADD(hour, 5, @TIMESTAMP);

DECLARE @DAYNUM as integer
DECLARE @DAYSECS as integer

SET @DAYNUM = datediff(day,cast('1970-01-01' as datetime), cast(floor(cast(@TIMESTAMP as float)) as datetime));
SET @DAYSECS = datediff(second ,cast(floor(cast(@TIMESTAMP as float)) as datetime), @TIMESTAMP);

select 24.0 * @DAYNUM * 3600.0 + @DAYSECS + datepart(ms,@TIMESTAMP)/1000.0 as UNIX;
0

Author Commented:
Here is what I am doing at the moment:

convert(float,DATEDIFF(second,'1969-12-31 19:00:00',[timestamp])) + convert(float,(DATEPART(MILLISECOND,[timestamp])))/1000 as time,

The reason why we have to do all these silly machinations is that datediff function returns an integer - it can't return a float, so we have to deal with milliseconds separately.

Any improvements on this?
0

Commented:
I know that this is an older post, but it may suit your needs. It goes into detail and provides examples of converting both to and from UNIX time.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=66858
0

Commented:
Try it this way:

This is how I tested it:

``````Declare @UNIXDateColumn numeric(18, 3)
Set @UNIXDateColumn = 1293975103.567