Skip to main content

Nifty SQL Server functions to convert to and from UNIX Time stamp

If you miss the from_unixtime() of mysql in MS SQL Server, then create these functions and enjoy.

The longtodate() function takes the number of milliseconds from epoch (Jan 1, 1970) and gives you the value in DATETIME format. To convert a DATETIME field to a unix timestamp use the datetolong() function



I. longtodate function

CREATE FUNCTION longtodate(@utc BIGINT)
RETURNS DATETIME
AS 
BEGIN
RETURN DATEADD(MILLISECOND, @utc % 1000, DATEADD(SECOND, @utc / 1000, '19700101'))
END  



II datetolong function

CREATE FUNCTION datetolong(@date DATETIME)
RETURNS BIGINT
AS 
BEGIN
RETURN CAST(DATEDIFF(s,'1970-01-01',@date) AS bigint) * 1000;
END



Comments

Post a Comment

Popular posts from this blog

Recently executed queries in SQL Server

To find out what all queries were executed recently in a sql server database, use the following queries Specific database: SELECT deqs.last_execution_time AS [Time], dest.text AS [Query], dest.* FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest WHERE dest.dbid = DB_ID(' msdb ') ORDER BY deqs.last_execution_time DESC   All Databases: SELECT deqs.last_execution_time AS [Time], dest.text AS [Query] FROM sys.dm_exec_query_stats AS deqs CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest ORDER BY deqs.last_execution_time DESC  
Post a Comment
Read more

Mundasupatti - a fun movie

I saw a movie the other day called Mundasupatti, I’m sure you’ve heard of it if you’re in Tamil Nadu. It was funny, they took a simple superstition which is around and made a fun movie out of it. It was a period film, exploring the 70’s of rural India. I was instantly reminded of swades, a great hindi film which again explores the development gap between cities and villages, it goes a bit further to compare development as seen by an Indian NASA engineer and his old house-hold nanny’s village.  While Swades was a serious film about self empowerment and braking society’s rules about casteism and encouraging education, Mundasupatti is just a funny movie about how stupid, people are.  The movie revolves around a village after which the film is named, the people in the village believe that taking a photograph causes people to get sick and die. The movie did a faithful representation of the rural India, with its proud people and crazy traditions which make no sense. People...
Post a Comment
Read more

Apache Nutch-Solr Integration

Apache Nutch-Solr Integration   As of this writing, I am using Solr 4.8.0 and Nutch 1.8.0 binaries for the integration. Will catch up to later versions as and when my project requires. We can cover installation and operation of Solr and Nutch separately and then talk about the integration. the version of Nutch that I am using is very closely built with Solr and the integration is very simple. For simplicity sake, Ill stick to Linux environment for both as Nutch does not operate in windows natively. Getting Solr to work 1. All you need to for Solr to work are the binaries. you can get them from their  official page  (version 4.8.0) 2. Extract the  solr-4.8.0.zip  in some location. for this tutorial, lets assume that its in  /home/test/Research/solr 4.8.0/ 3. Open terminal, navigate to  /home/test/Research/solr-4.8.0/example/  and execute the following command to start solr server java -jar start.jar  4...
1 comment
Read more