UNIX timestamp on MySQL and MSSQL Server

Question

How come there is a difference on timestamps in mysql and MS SQL Server
MySQL:

select now() result: 2013-07-16 08:49:26.0

SQL Server:

select getdate() result: 2013-07-16 08:48:16.47

and when I try to get unix_timestamp
MySQL:

SELECT UNIX_TIMESTAMP('2013-07-01') result: 1372629600

SQL Server:

SELECT DATEDIFF(SECOND,'1970-01-01', '2013-07-01') result 1372636800

problem: there is a difference of 7200 seconds (2h)
Is this a Time Zone problem, server time on both servers are the same as seen on now() and getdate()?

Answer 1

This is quite a long solution but it seems to work. It accounts for the offset in time zones.

SELECT DATEDIFF(SECOND, DATEADD(SECOND, DATEDIFF(SECOND, GETUTCDATE(), GETDATE()), '1970-01-01'), '2013-07-01')

Answer 2

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.

Answer 3

You can try this link for understanding timestamp in Mysql

http://www.mysqltutorial.org/mysql-timestamp.aspx

for tsql timestamp you can check out these links

http://www.informit.com/library/content.aspx?b=STY_Sql_Server_7&seqNum=97

http://technet.microsoft.com/en-us/library/ms188751(v=sql.110).aspx