|
1 |
originally posted 2013-7-25 |
A Unix timestamp is defined as the number of seconds elapsed between January 1, 1970 00:00:00 and a given date/time expressed in Universal Coordinated Time.
A timestamp can be generated up to the date 2038-1-19 3:14:7 before overflowing a 32-bit integer.
http://en.wikipedia.org/wiki/Unix_time
A timestamp can be generated up to the date 2038-1-19 3:14:7 before overflowing a 32-bit integer.
http://en.wikipedia.org/wiki/Unix_time
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
--create a Unix timestamp from current server UTC time create function [dbo].[fGetUnixTimestamp]() returns int as begin return datediff(s,'1970-1-1',sysutcdatetime()); end go --convert a Unix timestamp to a datetime in UTC create function [dbo].[fConvertUnixTimestampToDateTime] (@timestamp int) returns datetime as begin return dateadd(s,@timestamp,'1970-1-1') end go --convert a Unix timestamp to a datetime adjusted to the server's time zone create function [dbo].[fConvertUnixTimestampToLocalDateTime] (@timestamp int) returns datetime as begin return dateadd(hh,datediff(hh,sysutcdatetime(),getdate()),dateadd(s,@timestamp,'1970-1-1')) end go --[TEST] declare @ts int select @ts = [dbo].[fGetUnixTimestamp]() select [Timestamp] = @ts ,[UTC Time] = [dbo].[fConvertUnixTimestampToDateTime](@ts) ,[Local Time] = [dbo].[fConvertUnixTimestampToLocalDateTime](@ts) ,[Local Time Zone Offset from UTC] = datediff(hh,sysutcdatetime(),getdate()) /* [EXAMPLE RESULTS] Timestamp Local Time = 1374776005 UTC Time = Jul 25 2013 6:13PM Local Time = Jul 25 2013 11:13AM Local Time Zone Offset from UTC = -7 */ |
An alternative, encoding a DateTime into a BigInt and back again.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
create function [dbo].[fEncodeDateTimeToBigInt](@dt datetime) returns bigint as begin --note: resolution is to the second if(@dt is not null) return convert(bigint, DATEPART(second, @dt) + DATEPART(minute, @dt) * 100 + DATEPART(hour, @dt) * 10000 + DATEPART(day, @dt) * 1000000 + DATEPART(month, @dt) * 100000000 + DATEPART(year, @dt) * 10000000000) return null; end go create function [dbo].[fDecodeBigIntToDateTime](@bi bigint) returns datetime as begin if(@bi is not null) begin declare @str nvarchar(30) set @str = convert(nvarchar(30),@bi) if(len(@str)=14) return convert(datetime, substring(@str,1,4) + '-' + substring(@str,5,2) + '-' + substring(@str,7,2) + ' ' + substring(@str,9,2) + ':' + substring(@str,11,2) + ':' + substring(@str,13,2)); end return null; end --TEST declare @nowUtc datetime set @nowUtc = sysutcdatetime(); declare @bi bigint set @bi = [dbo].[fEncodeDateTimeToBigInt](@nowUtc); declare @dt datetime set @dt = [dbo].[fDecodeBigIntToDateTime](@bi); select [Input DateTime] = @nowUtc, [Encoded BigInt] = @bi, [Decoded DateTime] = @dt /* [Example Results] Input DateTime = 2013-07-25 20:55:30.293 Encoded BigInt = 20130725205530 Decoded DateTime = 2013-07-25 20:55:30.000 */ |