Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 Script Library
 UNIX Time Conversion Functions

Author  Topic 

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2006-05-28 : 18:18:34
A common problem in moving data between SQL Server and UNIX systems is converting to/from the SQL Server datetime format to the UNIX time format.

There are several UNIX time formats, but the most common is a signed 32-bit integer that represents time as the number of seconds between 1970-01-01 00:00:00 and a given time. The functions in the script can be use to convert between SQL Server datetime and this UNIX time format.

For more information on UNIX Time, please read this link:
http://en.wikipedia.org/wiki/Unix_time


For more information about SQL Server date/time conversions, refer to this link:
Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762



The conversion of UNIX Time to SQL Server datetime is fairly trivial using the SQL Server DATEADD function, and this is the logic used by the F_UNIX_TIME_TO_DATETIME function in the script:

declare @UNIX_TIME int
select @UNIX_TIME = 1111111111
-- Using dateadd to add seconds to 1970-01-01
select [Datetime from UNIX Time] = dateadd(ss,@UNIX_TIME,'1970-01-01')

Results:

Datetime from UNIX Time                                
------------------------------------------------------ 
2005-03-18 01:58:31.000

(1 row(s) affected)


The conversion of SQL Server datetime to UNIX Time is more complex. SQL Server datetime is accurate to milliseconds so is necessary to either truncate or round off the time to a whole second. The function in the F_DATETIME_TO_UNIX_TIME script rounds the time down if milliseconds is less than 500 and up otherwise. A second problem is that UNIX Time is an integer, so it can only represent time from 1901-12-13 20:45:52 through 2038-01-19 03:14:07. The range of SQL Server datetime is 1753-01-01 through 9999-12-31, so the function in the script has logic to return a NULL if the datetime is outside the valid UNIX Time range. Another minor issue is that the SQL Server DATEDIFF function will not cover the full range of an integer value with seconds, so it is necessary to have additional logic in the function do cover the time from 1901-12-13 20:45:52 to 1901-12-14 00:00:00.

The function names created by this script are:
dbo.F_DATETIME_TO_UNIX_TIME( @DAY )
dbo.F_UNIX_TIME_TO_DATETIME( @UNIX_TIME )

The script also includes code to test and demo the functions.




if objectproperty(object_id('dbo.F_DATETIME_TO_UNIX_TIME'),'IsScalarFunction') = 1
	begin drop function dbo.F_DATETIME_TO_UNIX_TIME end
go
create function dbo.F_DATETIME_TO_UNIX_TIME
	( @DAY datetime )
returns  int
as
/*
Function: F_DATETIME_TO_UNIX_TIME

	Finds UNIX time as the difference in seconds between
	1970-01-01 00:00:00 and input parameter @DAY after
	rounding @DAY to the neareast whoie second.

	Valid datetime range is 1901-12-13 20:45:51.500 through
	2038-01-19 03:14:07.497.  This range is limited to the smallest
	through the largest possible integer.

	Datetimes outside this range will return null.
*/
begin
declare @wkdt datetime

-- Return null if outside of valid UNIX Time range
if @DAY < '1901-12-13 20:45:51.500' or  @DAY > '2038-01-19 03:14:07.497'
	return null

-- Round off datetime to nearest whole second
select @wkdt = dateadd(ms,round(datepart(ms,@DAY),-3)-datepart(ms,@DAY),@DAY)

-- If date GE 1901-12-14
if @wkdt >= 712	return datediff(ss,25567,@wkdt) 

-- Handles time GE '1901-12-13 20:45:52.000 and LT 1901-12-14
return -2147472000-datediff(ss,@wkdt,712)

end
go
if objectproperty(object_id('dbo.F_UNIX_TIME_TO_DATETIME'),'IsScalarFunction') = 1
	begin drop function dbo.F_UNIX_TIME_TO_DATETIME end
go
create function dbo.F_UNIX_TIME_TO_DATETIME
	( @UNIX_TIME int )
returns  datetime
as
/*
Function: F_UNIX_TIME_TO_DATETIME

	Converts UNIX time represented as the difference
	in seconds between 1970-01-01 00:00:00 to a datetime.

	Any valid integer -2,147,483,648 through 2,147,483,647
	can be converted to datetime.
*/
begin

return  	dateadd(ss,@UNIX_TIME,25567)

end
go

go
/*
Demo functions F_DATETIME_TO_UNIX_TIME and 
F_UNIX_TIME_TO_DATETIME by converting a datetime
to UNIX time and back to datetime.
*/

select
	[Input Datetime] = convert(varchar(23),DT,121),
	[UNIX Time] = dbo. F_DATETIME_TO_UNIX_TIME(a.dt),
	[Datetime from UNIX Time] =
	-- Convert datetime to UNIX time an back to Datetime
	convert(varchar(23),
	dbo. F_UNIX_TIME_TO_DATETIME(dbo. F_DATETIME_TO_UNIX_TIME(a.dt)),121),
	Note = .a.note
from
(
select	DT = getdate(),
	Note = 'Current date'
union all
select	DT = dateadd(ms,500,getdate()),
	Note = 'Current date + 500 ms'
union all
select	DT = dateadd(ms,750,getdate()),
	Note = 'Current date + 750 ms'
union all
select	DT = '1901-12-13 20:45:51.500', 
	Note = 'Earliest datetime function can convert'
union all
select	DT = '2038-01-19 03:14:07.497',
	Note = 'Last datetime function can convert'
union all
select	DT = '2001-09-09 01:46:40',
	Note ='UNIX time 1000000000'
union all
select	DT = '2005-03-18 01:58:31',
	Note = 'UNIX time 1111111111'
union all
select	DT = '2009-02-13 23:31:30',
	Note ='UNIX time 1234567890'
union all
select	DT = '1901-12-14 00:00:00.000',
	Note = 'Date time dateadd second limit'
union all
select	DT = '1901-12-13 23:59:59.000',
	Note =  'Date time dateadd outside second limit'
union all
select	DT = '1901-12-13 20:45:51.497',
	Note = 'Date time function cannot convert - low end'
union all
select
	DT = '2038-01-19 03:14:07.500',
	Note = 'Date time function cannot convert - high end'
union all
select
	DT = '1753-01-01 00:00:00.000',
	Note = 'Min Datetime'
union all
select
	DT = '9999-12-31 23:59:59.997',
	Note = 'Max Datetime'
) a


Results:

Input Datetime          UNIX Time   Datetime from UNIX Time Note                                         
----------------------- ----------- ----------------------- -------------------------------------------- 
2006-05-29 23:34:11.517 1148945652  2006-05-29 23:34:12.000 Current date
2006-05-29 23:34:12.017 1148945652  2006-05-29 23:34:12.000 Current date + 500 ms
2006-05-29 23:34:12.267 1148945652  2006-05-29 23:34:12.000 Current date + 750 ms
1901-12-13 20:45:51.500 -2147483648 1901-12-13 20:45:52.000 Earliest datetime function can convert
2038-01-19 03:14:07.497 2147483647  2038-01-19 03:14:07.000 Last datetime function can convert
2001-09-09 01:46:40.000 1000000000  2001-09-09 01:46:40.000 UNIX time 1000000000
2005-03-18 01:58:31.000 1111111111  2005-03-18 01:58:31.000 UNIX time 1111111111
2009-02-13 23:31:30.000 1234567890  2009-02-13 23:31:30.000 UNIX time 1234567890
1901-12-14 00:00:00.000 -2147472000 1901-12-14 00:00:00.000 Date time dateadd second limit
1901-12-13 23:59:59.000 -2147472001 1901-12-13 23:59:59.000 Date time dateadd outside second limit
1901-12-13 20:45:51.497 NULL        NULL                    Date time function cannot convert - low end
2038-01-19 03:14:07.500 NULL        NULL                    Date time function cannot convert - high end
1753-01-01 00:00:00.000 NULL        NULL                    Min Datetime
9999-12-31 23:59:59.997 NULL        NULL                    Max Datetime

(14 row(s) affected)




Edit: Fixed minor bug that caused an overflow, instead of returning NULL, if input to function F_DATETIME_TO_UNIX_TIME was >= 9999-12-31 23:59:59.500.


CODO ERGO SUM

asadmehmoodstar
Starting Member

1 Post
Posted - 2011-12-01 : 05:42:14
thanks michael for this post. i have another simple solution for this. here is the solution
http://scriptstreasure.blogspot.com/2011/12/how-to-convert-unitx-timestamp-to-date.html
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts
Posted - 2011-12-01 : 10:30:21
quote:
Originally posted by asadmehmoodstar

thanks michael for this post. i have another simple solution for this. here is the solution
http://scriptstreasure.blogspot.com/2011/12/how-to-convert-unitx-timestamp-to-date.html


The link you posted is for MYSQL. This is a Microsoft SQL Server only forum; your solution will not work with any version of Microsoft SQL Server.



CODO ERGO SUM
Go to Top of Page

Aenos
Starting Member

1 Post
Posted - 2012-07-30 : 08:47:16
Maybe following expression is of some help.
It returns 0 if date is before 1970 and unix time else.
Anyhow it returns an integer.

CAST (((cast(DATEDIFF(hh, '1970-01-01 00:00:00', $date) AS dec(12,0))*60*60)
+ ABS (cast(DATEDIFF(hh, '1970-01-01 00:00:00', $date) AS dec(12,0))*60*60)) / 2 AS INTEGER)
Go to Top of Page

emmakeeling
Starting Member

1 Post
Posted - 2013-05-09 : 04:58:43
I need to convert all dates in a report to UNIX time - I am using Microsoft Visual Studio 2005.

I have looked at the forum details but have no idea where to put the phrase =DateDiff("s","1/1/1970 00:00",Fields!YOURFIELDHERE.Value) in order to convert the fields to UNIX time - I did not learn to write in SQL but by joining date views and queries in the grid pane so this is all quite new to me!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2013-05-09 : 08:00:16
quote:
Originally posted by emmakeeling

I need to convert all dates in a report to UNIX time - I am using Microsoft Visual Studio 2005.

I have looked at the forum details but have no idea where to put the phrase =DateDiff("s","1/1/1970 00:00",Fields!YOURFIELDHERE.Value) in order to convert the fields to UNIX time - I did not learn to write in SQL but by joining date views and queries in the grid pane so this is all quite new to me!

the above posted code is a SSRS expression which you've to put inside textbox created inside a container (table,matrix etc) in your SSRS report.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site