2,497
社区成员
发帖
与我相关
我的任务
分享
clientdataset1.CommandText :='SELECT l,r,dateadd(second ,t+datediff(second,getutcdate(),getdate()),{ts'1970-01-01 00:00:00'})
as t from tb';//假定t即为该时间字段
create function time_t_to_datetime(@time_t bigint)
returns Datetime
as
begin
return dateadd(second ,@time_t+datediff(second,getutcdate(),getdate()),{ts'1970-01-01 00:00:00'})
end
{ts'1970-01-01 08:00:00'}
这个时间常量的定义,本来unix time是指从1970年1月1日0时0分0秒以来的秒数.准确的应该是{ts'1970-01-01 00:00:00'}
但是这是GMT时间,如果需要转换为本地时间则需要增加时区,比如我们常的是+8时区,所以增加了8小时间.create function time_t_to_datetime(@time_t bigint)
returns Datetime
as
begin
return dateadd(second ,@time_t,{ts'1970-01-01 08:00:00'})
end
select dbo.time_t_to_datetime(cast(1226706555156 / 1000 as bigint))
clientdataset1.CommandText :='SELECT l,r,dbo.time_t_to_datetime(t / 1000)) as t from tb';//假定t即为该时间字段
create function fun()
returns bigint
as
begin
declare @unixtime bigint
declare @dt datetime
select @dt = currentTime from viw
select @unixtime = CONVERT(bigint,DATEDIFF(mi, '1970-01-01 00:00:00 ', @dt)) * 60 + DATEPART(ss,@dt)
return @unixtime
end
go