sql-server - SQL Server 日期时间到 bigint(纪元)溢出

Question

我有一个“日期时间”列，其值为 2013-03-22 15:19:02.000

我需要将此值转换为纪元时间并将其存储在“bigint”字段中

当我使用时，上述时间的实际纪元值为1363945741898

  select DATEDIFF(s, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

当我使用

时，我得到， 1363965542

select DATEDIFF(ms, '1970-01-01 00:00:00', '2013-03-22 15:19:02.000')

我明白了，

Msg 535, Level 16, State 0, Line 1 The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

如何从'datetime'字段获取准确的纪元值

我使用 SQL Server 2008。这也应该适用于 2005。

Answer 1

这是一个示例，未经测试，是徒手编写的:)

declare @v_Date datetime
set @v_Date = '2013-03-22 15:19:02.000'

declare @v_DiffInSeconds integer
declare @v_DiffInMSeconds bigint

select @v_DiffInSeconds = DATEDIFF(s, '1970-01-01 00:00:00', @v_Date)
select @v_DiffInMSeconds = cast(@v_DiffInSeconds as bigint) * 1000 + cast(DATEPART(ms, @v_Date) as bigint)

编辑我在下面做了这个例子来说明时区转换。给定的时间戳(以秒为单位，其中我删除了最后三位数字“898”)在这里通过添加 5.5 小时(19800 秒)转换为本地 IST 时区，然后将其转换回本地时间的时间戳又是格林尼治标准时间。下面的计算与问题中的值匹配(以秒为单位)。

declare @v_time datetime
set @v_time = '1970-01-01 00:00:00'

declare @v_date datetime
set @v_date = '2013-03-22 15:19:01'

-- This returns "March, 22 2013 15:19:01"
select dateadd(s, (1363945741 + 19800), @v_time)

-- This returns "1363945741"
select datediff(s, @v_time, @v_date) - 19800