Recently I was porting T-SQL (MSSQL) code into SQL dialect used by MySQL.
Process went smoothly until I have stuck with dates. Especially intervals between two dates. In T-SQL datediff function is used to get interval between to datetime values.
Let us consider this T-SQL sample:
declare @d1 datetime; declare @d2 datetime; set @d1 = '2009-01-18 15:22:01' set @d2 = '2009-01-19 14:22:01' select datediff(hour, @d1, @d2) as hour, datediff(day, @d1, @d2) as day, datediff(second, @d1, @d2) as secondQuery results are:
After doing some searching I found out that MySQL equivalent is:
set @d1 = '2009-01-18 15:22:01'; set @d2 = '2009-01-19 14:22:01'; select timestampdiff(hour, @d1, @d2) as hour, timestampdiff(day, @d1, @d2) as day, timestampdiff(second, @d1, @d2) as second;Query results are:
In general we can think of timestampdiff (MySQL) as 1-to-1 equivalent of datediff (MSSQL). To make them truly equal it is better to get difference in seconds and then convert (calculate) required interval (hours, days).