SQL时间戳

--临时表
CREATE TABLE #tmp (id INT,testtime timestamp)
--插入记录
INSERT INTO #tmp
        ( id )
VALUES  ( 1
          )
SELECT * FROM #tmp
--时间戳转int
SELECT CAST(testtime AS INT) FROM #tmp
SELECT CAST(CAST(testtime AS INT) AS TIMESTAMP) FROM #tmp
DROP TABLE #tmp
--datetime转unix时间戳
SELECT DATEDIFF(s, '19700101',GETDATE()) 
--unix时间戳转datetime
SELECT DATEADD(s, 1509099003 , '19700101')
--二进制时间
SELECT convert(timestamp,GETDATE())
--int 时间
SELECT CAST (convert(timestamp,GETDATE()) AS INT)
--datetime时间
SELECT DATEADD(s, CAST(convert(timestamp,GETDATE()) AS INT) , '19700101')
--数据库当前时间戳值
SELECT  @@DBTS

--普通时间  转  13 位时间戳
SELECT CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 00:00:00.000', GETUTCDATE())) * 60000 + DATEPART(S,GETUTCDATE()) * 1000 + DATEPART(MS, GETUTCDATE())

--13位时间戳 转 普通时间
SELECT DATEADD (MS ,CONVERT(BIGINT,1582687127323) % 60000 ,DATEADD(MI,CONVERT(BIGINT,1582687127323) / 60000,'1970-01-01 08:00:00.000'))

SELECT DATEDIFF(S,'1970-01-01 00:00:00', GETDATE()) 
--10位时间戳转换成普通时间
SELECT DATEADD(S,1557493321,'1970-01-01 00:00:00') 
SELECT 
CONVERT(BIGINT,DATEDIFF(MI,'1970-01-01 08:00:00.000', GETUTCDATE())) * 60000 + DATEPART(S,GETUTCDATE()) * 1000 + DATEPART(MS, GETUTCDATE())