How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
This worked for me:
Select
dateadd(S, [unixtime], '1970-01-01')
From [Table]
In case any one wonders why 1970-01-01, This is called Epoch time.
Below is a quote from Wikipedia:
The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.
The Year 2038 Problem
Furthermore the DataAdd function takes an int
for the seconds to add. So if you are trying to add more than 2147483647
seconds you'll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.
Declare @t as bigint = 4147483645
Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add
-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))
This will let you convert a timestamp that represents a year greater than 2038.
Convert unix epoch timestamp to TSQL datetime
Easy, first add whole days, then add the remaining ms. There are 86,400,000 milliseconds in a day.
declare @unixTS bigint
set @unixTS = 1359016610667
select dateadd(ms, @unixTS%(3600*24*1000),
dateadd(day, @unixTS/(3600*24*1000), '1970-01-01 00:00:00.0')
)
The result is 2013-01-24 08:36:50.667
Convert bigint to datetime
Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:
select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))
I've divided by 1000 because the dateadd function won't work with a number that large. So you do lose a little precision, but it is much simpler to use.
Convert Bigint data to human readable date-time in Sql
The value 1546380001264082944
is too big to be epoch seconds or even milliseconds. This is easily verified by putting this value on https://currentmillis.com/.
You have stored precision upto a nanosecond. So, divide the column value by 1e9 before passing them to from_unixtime
.
SELECT INN, from_unixtime(MIN(Time) / 1000000000), from_unixtime(MAX(Time) / 1000000000)
FROM DB
GROUP BY INN
Convert bigint epoch in SQL Server
If you don't have epoch values greater than 5103129600
(which will allow you you to have dates up to 2106-02-07T06:28:13
) this will work:
SELECT DATEADD(SECOND,2551564800 % 2147483647, DATEADD(SECOND,2147483647 * (V.Epoch / 2147483647),'19700101'))
FROM (VALUES(CONVERT(bigint,2551564800))) V(Epoch)
Related Topics
Storing Image in Database Directly or as Base64 Data
Select Rows Having 2 Columns Equal Value
How to Return the Most Recent Logtime for Each Sku
Number of Times a Value Appears (Sql)
Postgres: Insert If Does Not Exist Already
Iif Statement With Multiple Conditions
How to Update Millions of Records in MySQL
Detect If Value Is Number in MySQL
Sql Query to Select from 1 Hour Ago
Spark - Query Dataframe Based on Values from a Column in Another Dataframe
Sql to Generate a List of Numbers from 1 to 100
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
How to Get Last 7 Days Data from Current Datetime to Last 7 Days in SQL Server
Select Distinct Values from One Table and Join With Another Table
How to Convert Milliseconds to Time(Hh:Mm:Ss) in Oracle
How to Merge Multiple Rows into Single in Oracle