DATEDIFF to TIMESTAMPDIFF - Sybase ASE to MariaDB Migration

In Sybase ASE you can use DATEDIFF function to get the difference between two datetime values in the specified interval units (days, hours, minutes etc.). In MariaDB you can use TIMESTAMPDIFF function, but note that the interval unit specifiers can be different:

Sybase ASE:

  -- Get difference in days between two datetimes
  SELECT DATEDIFF(dd, '2017-12-16', '2017-12-31')
  # 15


  -- Get difference in days between two datetimes
  SELECT TIMESTAMPDIFF(day, '2017-12-16', '2017-12-31');
  # 15

Mapping Sybase ASE DATEDIFF Interval Units to MariaDB

You can use SQLines SQL Converter to convert Sybase ASE DATEDIFF function to TIMESTAMPDIFF that maps the interval units to the appropriate values in MariaDB:

Sybase ASE MariaDB
yy year Difference in years year
qq quarter Difference in quarters quarter
mm month Difference in months month
wk week Difference in weeks week
dd day Difference in days day
hh hour Difference in hours hour
mi minute Difference in minutes minute
ss second Difference in seconds second
ms millisecond Difference in milliseconds 1/1000 microsecond
us microsecond Difference in microseconds microsecond

Conversion examples:

Sybase ASE MariaDB
1 SELECT DATEDIFF(mi, GETDATE(), '2017-12-31') SELECT TIMESTAMPDIFF(minute, NOW(), '2017-12-31');
2 SELECT DATEDIFF(hh, GETDATE(), '2017-12-31') SELECT TIMESTAMPDIFF(hour, NOW(), '2017-12-31');
3 SELECT DATEDIFF(mm, GETDATE(), '2017-12-31') SELECT TIMESTAMPDIFF(month, NOW(), '2017-12-31');

For more information, see: