asked on

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.

gnf

ASKER

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?

Jerry Miller

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

Anthony Perkins

Try it this way:

SELECT DATEADD(millisecond, (UNIXDateColumn * 1000) % 1000, DATEADD(second, UNIXDateColumn, '1970-01-01'))

This is how I tested it:

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

SELECT DATEADD(millisecond, (@UNIXDateColumn * 1000) % 1000, DATEADD(second, @UNIXDateColumn, '1970-01-01'))

Open in new window

ASKER CERTIFIED SOLUTION

deighton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial