INTERVAL MINUTE TO MINUTE - Informix to PostgreSQL Migration

In Informix INTERVAL MINUTE TO MINUTE data type stores a time interval in minutes.

As a data type it just stores an integer value, but when you cast any interval value (INTERVAL DAY TO SECOND i.e.) to ::INTERVAL MINUTE TO MINUTE, you get the total number of minutes in the interval.

Consider the following example:

Informix:

  -- Get time interval between two HH:MI:SS time values
  SELECT DATETIME(11:10:09) HOUR TO SECOND - DATETIME(10:00:00) HOUR TO SECOND 
  FROM systables WHERE tabid = 1; 
  # 1:10:09
 
  -- Now cast it to INTERVAL MINUTE TO MINUTE 
  SELECT (DATETIME(11:10:09) HOUR TO SECOND - DATETIME(10:00:00) HOUR TO SECOND) 
               ::INTERVAL MINUTE(5) TO MINUTE 
  FROM systables WHERE tabid = 1; 
  # 70

You can see that we got 70 minutes when the interval '1:10:09' was cast to INTERVAL MINUTE TO MINUTE.

Convert INTERVAL MINUTE TO MINUTE Data Type to PostgreSQL

When used as a data type INTERVAL MINUTE TO MINUTE stores the number of minutes in Informix, so you can use an integer data type in PostgreSQL.

Note that INTERVAL MINUTE in PostgreSQL specifies the precision of the interval value and contains interval items up to minutes (day, hour and minute), and seconds set to zero.

Consider the following example:

PostgreSQL:

  SELECT '1 day 15:11:10.123'::INTERVAL MINUTE
  # 1 day 15:11:00

You can see that cast INTERVAL MINUTES still includes items such as day, hour, minute and second in PostgreSQL.

Get the Total Number of Minutes in PostgreSQL

You can use EXTRACT(EPOCH FROM interval_value)/60 expression to get the total number of minutes in an interval value in PostgreSQL:

PostgreSQL:

  -- Get total minutes in '1 day 15:11:10.123'
  SELECT EXTRACT(EPOCH FROM '1 day 15:11:10.123'::INTERVAL)/60
  # 2351.16871666667
 
  -- Truncate to get an integer
  SELECT TRUNC(EXTRACT(EPOCH FROM '1 day 15:11:10.123'::INTERVAL)/60)
  # 2351
 
  -- Number of minutes between '11:10:09' and '10:00:00'
  SELECT TRUNC(EXTRACT(EPOCH FROM (TIME '11:10:09' - TIME '10:00:00'))/60) 
  # 70

EXTRACT(EPOCH FROM interval_value) returns the number of seconds, then the result is divided by 60 to get the number of minutes.

Database and SQL Migration Tools

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - October 2013.

You could leave a comment if you were logged in.