INTERVAL SECOND TO SECOND - Informix to PostgreSQL Migration

In Informix INTERVAL SECOND TO SECOND data type stores a time interval in seconds.

As a data type it just stores an integer value, but the most interesting thing about this data type is that when you cast any interval value (INTERVAL DAY TO SECOND i.e.) to ::INTERVAL SECOND TO SECOND, you get the total number of seconds 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 SECOND TO SECOND 
  SELECT (DATETIME(11:10:09) HOUR TO SECOND - DATETIME(10:00:00) HOUR TO SECOND) 
               ::INTERVAL SECOND(5) TO SECOND 
  FROM systables WHERE tabid = 1; 
  # 4209

You can see that we got 4209 seconds when the interval '1:10:09' was cast to INTERVAL SECOND TO SECOND.

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

Note that INTERVAL SECOND(n) in PostgreSQL specifies the precision of the interval value and contains interval items up to seconds with fraction (day, hour, minute, second and fraction), not only seconds.

Consider the following example:

PostgreSQL:

  SELECT '1 day 15:11:10.123'::INTERVAL SECOND
  # 1 day 15:11:10.123
 
  -- Truncate fraction
  SELECT '1 day 15:11:10.123'::INTERVAL SECOND(0)
  # 1 day 15:11:10

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

You can use EXTRACT(EPOCH FROM interval_value) function to get the total number of seconds in an interval value in PostgreSQL:

PostgreSQL:

  -- Get total seconds in '1 day 15:11:10.123'
  SELECT EXTRACT(EPOCH FROM '1 day 15:11:10.123'::INTERVAL)
  # 141070.123
 
  -- Without fraction
  SELECT EXTRACT(EPOCH FROM '1 day 15:11:10'::INTERVAL)
  # 141070
 
  -- Number of seconds between '11:10:09' and '10:00:00'
  SELECT EXTRACT(EPOCH FROM (TIME '11:10:09' - TIME '10:00:00')) 
  # 4209

• INTERVAL MINUTE TO MINUTE - Informix to PostgreSQL Migration

• Informix to PostgreSQL Migration Tools and Reference
• Online SQL Conversion Tool

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.