Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert Datetime to Unix timestamp

In Microsoft SQL Server 2012 or above, is it possible to convert a datetime value to Unix time stamp in a single select statement? If so, how can it be done?

like image 994
O A Avatar asked Dec 24 '15 16:12

O A


People also ask

How do I manually convert a date to a timestamp in Unix?

Select a blank cell, suppose Cell C2, and type this formula =(C2-DATE(1970,1,1))*86400 into it and press Enter key, if you need, you can apply a range with this formula by dragging the autofill handle. Now a range of date cells have been converted to Unix timestamps.

How convert UTC timestamp to Unix in Python?

DateTime to Unix timestamp in UTC Timezone In the time module, the timegm function returns a Unix timestamp. The timetuple() function of the datetime class returns the datetime's properties as a named tuple. To obtain the Unix timestamp, use print(UTC).

How do I convert Unix time to normal time?

To easily convert UNIX timestamp to date in the . csv file, do the following: 1. =R2/86400000+DATE(1970,1,1), press Enter key.

Is UTC Unix time?

Unix time is a way of representing a timestamp by representing the time as the number of seconds since January 1st, 1970 at 00:00:00 UTC. One of the primary benefits of using Unix time is that it can be represented as an integer making it easier to parse and use across different systems.


2 Answers

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());
like image 93
khaled4vokalz Avatar answered Sep 18 '22 05:09

khaled4vokalz


maybe this answer will help someone... If you have a problem when you try to convert datetime using datediff function to number of seconds (mssql message: 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.) then use:

select cast(datediff(d,'1970-01-01',@d1) as bigint)*86400+datediff(s,dateadd(day, datediff(day, 0, @d1), 0),@d1)

if you have ms sql server 2016+ use DATEDIFF_BIG function

like image 36
Aleksandr Avatar answered Sep 19 '22 05:09

Aleksandr