Solved

How do i calculate unix time from sql datetime

Posted on 2011-02-26
4
1,181 Views
Last Modified: 2012-08-13
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.


0
Comment
Question by:gnf
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 

Author Comment

by:gnf
ID: 34988731
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
 
LVL 18

Expert Comment

by:Jerry Miller
ID: 34990079
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34994439
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

0
 
LVL 18

Accepted Solution

by:
deighton earned 250 total points
ID: 34996246
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

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to take different types of Oracle backups using RMAN.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question