I have timestamps which are stored as seconds since epoch, (Jan 1,
1970 00:00:00 GMT) that I need to be able to work with in human
readable format. e.g. yyyy-mm-dd hh:mm:ss

I would like to have a couple of stored procedures that convert
datestrs to epoch and vice versa.  Having an optional argument for the
timezone is a plus.

I have searched for anything online that I could use to build this, to
no avail.

I think this is a relatively simple exercise with SQL's powerful date
functions.  In one format, you're given the number of milliseconds
since an epoch and wish to find a day/month/year/time format.  In the
reverse, you're given a day/month/year/time format and wish to find
the number of milliseconds since the epoch.

Essentially, we'll use the DATEADD and DATEDIFF functions,
respectively, to solve these problems.  For more information on these
functions be sure to review the SQL Books Online.

To convert from the number of milliseconds to a date/month/year/time
format, we simply use DATEADD to add the number of seconds to the
epoch (01-01-1970 00:00:00), like so:

CREATE PROCEDURE epcohToHumanFriendly(
	@numMilliseconds bigint
)
 AS


-- Now, find out how many seconds have existed between now and the
epoch
SELECT @numMilliseconds = @numMilliseconds / 1000
SELECT DATEADD(ss, @numMilliseconds, '01-01-1970 00:00:00')
GO


Note that the input parameter is  bigint, but I need to convert it to
seconds otherwise I'll get an int overflow in the DATEADD.

To do the converse, just use DATEDIFF to determine the number of
seconds that has transpired from the human-friendly date passed in and
then multiplying that by 1000.

CREATE PROCEDURE humanFriendlyToEpoch
(
	@hfDate datetime
)
 AS


-- Now, find out how many seconds have existed between now and the
epoch
SELECT CONVERT(bigint, DATEDIFF(dd,  '01-01-1970 00:00:00', @hfDate))
* 24 * 3600 * 1000
GO


hth

---

Clarification of Answer by skmitchell-ga on 26 Jun 2002 10:44 PDT

Whoops, small typo in the humanFriendlyToEpoch sproc, I ignore the
hours/minutes/seconds part, gimmie a second to "fix" the code, please.

---

Clarification of Answer by skmitchell-ga on 26 Jun 2002 10:45 PDT

The fix:  simply change the SELECT statement in humanFriendlyToEpoch to:

SELECT CONVERT(bigint, DATEDIFF(ss,  '01-01-1970 00:00:00', @hfDate)) * 1000

hth, and sorry for the minor bug!  :-)

In general I am very pleased with this answer.  It took very little
tweaking to do exactly what I needed.  I never even considered using
SQL's built in functions because when I first researched them, I
discovered that they consider the epoch to be 1900, so I didn't think
they would be useful.  Silly me!

For posterity's sake, here is the final result code.  I decided to use
user-defined functions instead of sprocs.

create function date2timestamp( 
	@dateStr datetime
)
returns bigint
as	begin
-- Now, find out how many seconds have existed between now and the
epoch
	return convert(bigint,
				datediff(ss,  '01-01-1970 00:00:00', @dateStr))
	end
go

create function timestamp2date( 
	@numSeconds bigint
)
returns varchar(20)
as	begin
--	set @numSeconds = @numSeconds + (60 * 60 * -5) -- subtract EST
-- Now, find out how many seconds have existed between @numSeconds and
the epoch
		return dateadd(ss, @numSeconds, '01-01-1970 00:00:00') 
	end
go