UNIX timestamp on MySQL and MSSQL Server
mysql, t-sql, timestamp, timezone
Solution
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.
Problem
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()`?