Migrate PostgreSQL views to MySQL

When migrating database from MySQL to PostgreSQL one of the most complicated steps is conversion of views. This whitepaper discovers basic rules of this conversion.

1. PostgreSQL built-in function AGE must be mirated into MySQL as follows:

   DELIMITER $$
    
   CREATE OR REPLACE FUNCTION age(date_end datetime, date_start datetime)
   RETURNS VARCHAR(100)
   BEGIN
       DECLARE res VARCHAR(100) DEFAULT '';
       DECLARE n_years, n_months, n_days, n_hours, n_minutes, n_seconds INT;
    
       SET n_years  = TIMESTAMPDIFF(YEAR, date_start, date_end);
       SET date_end = date_end - INTERVAL n_years YEAR;
    
       if (n_years > 1) then
           SET res = CONCAT(n_years, ' years ');
       elseif (n_years = 1) then
           SET res = '1 year ';
       end if;
    
       SET n_months = TIMESTAMPDIFF(MONTH, date_start, date_end);
       SET date_end = date_end - INTERVAL n_months MONTH;
    
       if (n_months > 1) then
           SET res = CONCAT(res, n_months, ' mons ');
       elseif (n_months = 1) then
           SET res = CONCAT(res, '1 mon ');
       end if;
    
       SET n_days = TIMESTAMPDIFF(DAY, date_start, date_end);
       SET date_end = date_end - INTERVAL n_days DAY;
       if (n_days > 1) then
           SET res = CONCAT(res, n_days, ' days ');
       elseif (n_days = 1) then
           SET res = CONCAT(res, '1 day ');
       end if;
    
       SET n_hours = TIMESTAMPDIFF(HOUR, date_start, date_end);
       SET date_end = date_end - INTERVAL n_hours HOUR;
       SET n_minutes = TIMESTAMPDIFF(MINUTE, date_start, date_end);
       SET date_end = date_end - INTERVAL n_minutes MINUTE;
       SET n_seconds = TIMESTAMPDIFF(SECOND, date_start, date_end);
       SET date_end = date_end - INTERVAL n_seconds SECOND;
    
       if (n_hours > 0 or n_minutes > 0 or n_seconds > 0) 
       then
           SET res = CONCAT(res, LPAD(n_hours, 2, 0), ':', 
               LPAD(n_minutes, 2, 0), ':', LPAD(n_seconds, 2, 0));
       end if;
       return res;
   END $$
    
   DELIMITER ;
   

2. PostgreSQL expression $date + $interval must be converted into MySQL function call DATE_ADD($date, INTERVAL $interval_units, ) according to the table below:

   PostgreSQL Interval		MySQL Equivalent
   ($date + $n_units * interval '1 day')::date		INTERVAL $n_units DAY
   ($date + $n_units * interval '1 hour')::date		INTERVAL $n_units HOUR
   ($date + $n_units * interval '1 minute')::date		INTERVAL $n_units MINUTE
   ($date + $n_units * interval '1 month')::date		INTERVAL $n_units MONTH
   ($date + $n_units * interval '1 second')::date		INTERVAL $n_units SECOND
   ($date + $n_units * interval '1 week')::date		INTERVAL $n_units WEEK
   ($date + $n_units * interval '1 year')::date		INTERVAL $n_units YEAR

3. Every occurrence of DATE_PART($date_part,$date) must be replaced by EXTRACT($date_part FROM $date)
4. PostgreSQL type casting operators expression::type must be converted into MySQL CAST(expression AS type) according to the types mapping table.

Have questions? Contact us