SQL Server 中的 UNIX_TIMESTAMP
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8837225/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UNIX_TIMESTAMP in SQL Server
提问by TheZver
I need to create a function in SQL Server 2008 that will mimic mysql's UNIX_TIMESTAMP().
我需要在 SQL Server 2008 中创建一个函数来模拟 mysql 的UNIX_TIMESTAMP().
Thanks in advance !
提前致谢 ！
采纳答案by rkosegi
Try this post: https://web.archive.org/web/20141216081938/http://skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/
试试这个帖子：https: //web.archive.org/web/20141216081938/http: //skinn3r.wordpress.com/2009/01/26/t-sql-datetime-to-unix-timestamp/
CREATE FUNCTION UNIX_TIMESTAMP (
@ctimestamp datetime
)
RETURNS integer
AS
BEGIN
/* Function body */
declare @return integer
SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)
return @return
END
or this post:
或者这个帖子：
code is as follows:
代码如下：
CREATE FUNCTION dbo.DTtoUnixTS
(
@dt DATETIME
)
RETURNS BIGINT
AS
BEGIN
DECLARE @diff BIGINT
IF @dt >= '20380119'
BEGIN
SET @diff = CONVERT(BIGINT, DATEDIFF(S, '19700101', '20380119'))
+ CONVERT(BIGINT, DATEDIFF(S, '20380119', @dt))
END
ELSE
SET @diff = DATEDIFF(S, '19700101', @dt)
RETURN @diff
END
Sample usage:
示例用法：
SELECT dbo.DTtoUnixTS(GETDATE())
-- or
SELECT UnixTimestamp = dbo.DTtoUnixTS(someColumn)
FROM someTable
回答by Dunc
If you're not bothered about dates before 1970, or millisecond precision, just do:
如果您不担心 1970 年之前的日期或毫秒精度，请执行以下操作：
-- SQL Server
SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)
Almost as simple as MySQL's built-in function:
几乎和 MySQL 的内置函数一样简单：
-- MySQL
SELECT UNIX_TIMESTAMP(DateField);
Other languages (Oracle, PostgreSQL, etc): How to get the current epoch time in ...
其他语言（Oracle、PostgreSQL 等）：如何在...中获取当前纪元时间
回答by Randall Sutton
Sql Server 2016 and later have a DATEDIFF_BIG function that can be used to get the milliseconds.
Sql Server 2016 及更高版本有一个 DATEDIFF_BIG 函数，可用于获取毫秒。
SELECT DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())
Create a function
创建函数
CREATE FUNCTION UNIX_TIMESTAMP()
RETURNS BIGINT
AS
BEGIN
RETURN DATEDIFF_BIG(millisecond, '1970-01-01 00:00:00', GETUTCDATE())
END
And execute it
并执行它
SELECT dbo.UNIX_TIMESTAMP()
回答by Robert Calhoun
I often need a unix timestamp with millisecond precision. The following will give you the current unixtime as FLOAT; wrap per answers above to get a function or convert arbitrary strings.
我经常需要一个毫秒精度的unix时间戳。以下将为您提供当前的 unixtime FLOAT；包装上面的每个答案以获取函数或转换任意字符串。
The DATETIMEdatatype on SQL Server is only good to 3 msec, so I have different examples for SQL Server 2005 and 2008+. Sadly there is no DATEDIFF2function, so various tricks are required to avoid DATEDIFFinteger overflow even with 2008+. (I can't believe they introduced a whole new DATETIME2datatype without fixing this.)
DATETIMESQL Server 上的数据类型仅适用于 3 毫秒，因此我为 SQL Server 2005 和 2008+ 提供了不同的示例。遗憾的是没有DATEDIFF2函数，因此DATEDIFF即使使用 2008+也需要各种技巧来避免整数溢出。（我不敢相信他们在DATETIME2不解决这个问题的情况下引入了一种全新的数据类型。）
For regular old DATETIME, I just use a sleazy cast to float, which returns (floating point) number of days since 1900.
对于普通的 old DATETIME，我只是使用一个低俗的演员来浮动，它返回（浮点）自 1900 年以来的天数。
Now I know at this point, you are thinking WHAT ABOUT LEAP SECONDS?!?! Neither Windows time nor unixtime really believe in leap seconds: a day is always 1.00000 days long to SQL Server, and 86400 seconds long to unixtime. This wikipedia articlediscusses how unixtime behaves during leap seconds; Windows I believe just views leap seconds like any other clock error. So while there is no systematic drift between the two systems when a leap second occurs, they will not agree at the sub-second level during and immediately following a leap second.
现在我知道在这一点上，你在想什么是闰秒？！？！Windows 时间和 unixtime 都不相信闰秒：一天对于 SQL Server 来说总是 1.00000 天，对于 unixtime 来说总是 86400 秒。这篇维基百科文章讨论了 unixtime 在闰秒期间的行为；Windows 我相信只是像任何其他时钟错误一样查看闰秒。因此，虽然当闰秒发生时两个系统之间没有系统漂移，但它们在闰秒期间和紧随其后的亚秒级不会一致。
-- the right way, for sql server 2008 and greater
declare @unixepoch2 datetime2;
declare @now2 Datetime2;
declare @days int;
declare @millisec int;
declare @today datetime2;
set @unixepoch2 = '1970-01-01 00:00:00.0000';
set @now2 = SYSUTCDATETIME();
set @days = DATEDIFF(DAY,@unixepoch2,@now2);
set @today = DATEADD(DAY,@days,@unixepoch2);
set @millisec = DATEDIFF(MILLISECOND,@today,@now2);
select (CAST (@days as float) * 86400) + (CAST(@millisec as float ) / 1000)
as UnixTimeFloatSQL2008
-- Note datetimes are only accurate to 3 msec, so this is less precise
-- than above, but works on any edition of SQL Server.
declare @sqlepoch datetime;
declare @unixepoch datetime;
declare @offset float;
set @sqlepoch = '1900-01-01 00:00:00';
set @unixepoch = '1970-01-01 00:00:00';
set @offset = cast (@sqlepoch as float) - cast (@unixepoch as float);
select ( cast (GetUTCDate() as float) + @offset) * 86400
as UnixTimeFloatSQL2005;
-- Future developers may hate you, but you can put the offset in
-- as a const because it isn't going to change.
declare @sql_to_unix_epoch_in_days float;
set @sql_to_unix_epoch_in_days = 25567.0;
select ( cast (GetUTCDate() as float) - @sql_to_unix_epoch_in_days) * 86400.0
as UnixTimeFloatSQL2005MagicNumber;
FLOATs actually default to 8-byte doubles on SQL Server, and therefore superior to 32-bit INTfor many use cases. (For example, they won't roll over in 2038.)
FLOAT 在 SQL Server 上实际上默认为 8 字节双精度，因此INT在许多用例中优于 32 位。（例如，它们不会在 2038 年滚动。）
回答by Stefan Steiger
Necromancing.
The ODBC-way:
死灵法术。
ODBC方式：
DECLARE @unix_timestamp varchar(20)
-- SET @unix_timestamp = CAST({fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, CURRENT_TIMESTAMP)} AS varchar(20))
IF CURRENT_TIMESTAMP >= '20380119'
BEGIN
SET @unix_timestamp = CAST
(
CAST
(
{fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, {d '2038-01-19'})}
AS bigint
)
+
CAST
(
{fn timestampdiff(SQL_TSI_SECOND,{d '2038-01-19'}, CURRENT_TIMESTAMP)}
AS bigint
)
AS varchar(20)
)
END
ELSE
SET @unix_timestamp = CAST({fn timestampdiff(SQL_TSI_SECOND,{d '1970-01-01'}, CURRENT_TIMESTAMP)} AS varchar(20))
PRINT @unix_timestamp
回答by eranda.del
When called to Scalar-valued Functions can use following syntax
当调用标量值函数时，可以使用以下语法
Function Script :
函数脚本：
USE [Database]
GO
/****** Object: UserDefinedFunction [dbo].[UNIX_TIMESTAMP] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[UNIX_TIMESTAMP] (
@ctimestamp datetime
)
RETURNS integer
AS
BEGIN
/* Function body */
declare @return integer
SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)
return @return
END
GO
Call Function :
呼叫功能：
SELECT dbo.UNIX_TIMESTAMP(GETDATE());
