Sunday, December 13, 2009

MSSQL DATEDIFF Equivalent in MySQL

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 second
Query results are:
hourdaysecond
23182800

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:
hourdaysecond
23082800
Query results are nearly the same except day difference. Somehow, MSSQL treats 23 hours as one day.

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).

No comments:

Post a Comment