SQL Server日期时间到bigint（epoch）溢出 [英] SQL Server datetime to bigint (epoch) overflow

问题描述

我有一个'datetime'列，其值是2013-03-22 15：19：02.000

I have a 'datetime' column with value 2013-03-22 15:19:02.000

我需要将此值转换为纪元时间并将其存储在一个'bigint'字段

I need to convert this value into epoch time and store it in a 'bigint' field

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

The actual epoch value for the above time is, 1363945741898, when I use

  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')

我明白了，

消息535，级别16，状态0，第1行
datediff函数导致溢出。分隔两个日期/时间实例的日期部分的数量太大。尝试使用日期精度较低的datediff。

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.

如何从获取精确的历元值 strong>'datetime'字段

How to get the exact epoch value from the 'datetime' field

我使用SQL Server2008。这也适用于2005年。

I use SQL Server 2008. Also this should work with 2005.

推荐答案

下面是一个示例，未经测试，是徒手编写的：）

Here is an example, not tested, written from free hand :)

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时区，然后将其转换为本地时间到格林尼治标准时间再次出现。下面的计算与问题中的值匹配（以秒为单位）。

Edit I have made this example below to illustrate the time zone conversion. The given time stamp (in seconds where I have removed the last three digits "898") is here converted to the local IST time zone by adding the 5.5 hours (19800 seconds) and I convert it back to the time stamp from local time to GMT again. Below calculations matches the values in the question (in seconds).

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

这篇关于SQL Server日期时间到bigint（epoch）溢出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！