Unix_Timestamp in SQL Server

UNIX_TIMESTAMP in SQL Server

If you're not bothered about dates before 1970, or millisecond precision, just do:

-- SQL Server
SELECT DATEDIFF(s, '1970-01-01 00:00:00', DateField)

Almost as simple as MySQL's built-in function:

-- MySQL
SELECT UNIX_TIMESTAMP(DateField);

Other languages (Oracle, PostgreSQL, etc): How to get the current epoch time in ...

---

If you need millisecond precision (SQL Server 2016/13.x and later):

SELECT DATEDIFF_BIG(ms, '1970-01-01 00:00:00', DateField)

UNIX timestamp on MySQL and MSSQL Server

On SQL Server use

select datediff(ss, '1970-01-01', SYSDATETIMEOFFSET() at time zone 'UTC')


Here,

SYSDATETIMEOFFSET() returns the local system time with the local time zone offset enabling the conversion to UTC. Replace it with your own datetimeoffset field if you want something other than the current time.

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 Datetime to Unix timestamp

As Peter Halasz mentions in T-SQL DateTime to Unix Timestamp:

Converting a datetime to unix timestamp is easy, but involves error
prone typing the following:

@timestamp=DATEDIFF(second,{d '1970-01-01'},@datetime)

Where @datetime is the datetime value you want to convert. The {d
‘yyyy-mm-dd’} notation is an ODBC escape sequence.

The function:

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

Try it out now like below @O A:

SELECT UNIX_TIMESTAMP(GETDATE());

MSSQL bigint Unix Timestamp to Datetime with milliseconds

I think that you are dealing with nanosecond precision. What you can get in native sql is 100ns precision.

declare @ts as bigint = 1462924862735870900

select dateadd(NANOSECOND, @ts % 1000000000, dateadd(SECOND, @ts / 1000000000, cast('1970-01-01' as datetime2(7))))

The outcome is 2016-05-11 00:01:02.7358709

How can I select data from this week using a Unix timestamp in SQL?

You can convert the filter value to a unix timestamp with date function unixtimestamp(), like so:

where dtp_s > unix_timestamp(now() - interval 1 week)

---

Related Topics