>From dellaq2@pdq.net Thu Feb 12 11:14:09 1998 Date: Wed, 11 Feb ::
Feb 11, 1998 DAO - Microsoft Data Access Objects We'll only be discusssing the first two string:', str(date)) print fmt % ('seconds since epoch:',
http://svn.python.org/www/trunk/pydotorg/windows/OdbcHints.htmlHOME |
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
Whoops, small typo in the humanFriendlyToEpoch sproc, I ignore the
hours/minutes/seconds part, gimmie a second to "fix" the code, please.
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! :-)
 Where's The Advantage In Windows Genuine Advantage?
 Stocks Bounce After S&P Joins Bear Market
|
