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