# INTERVAL data type

## Informix®

Informix provides the `INTERVAL`

data type to store a value that represents a
span of time.

`INTERVAL`

types are divided into two classes:- year-month intervals. For example:
`INTERVAL YEAR(5) TO MONTH`

- day-time intervals. For example:
`INTERVAL DAY(9) TO SECOND`

`INTERVAL`

columns can be defined with various time units, by specifying a
start and end qualifier. For example, you can define an interval to store a number of hours and
minutes with `INTERVAL HOUR(``n`) TO MINUTE

, where
`n`defines the maximum number of digits for the hours unit.

The values of Informix

`INTERVAL`

can be represented with a
character string literal, or as `INTERVAL()`

literals:```
'-9834 15:45:12.345' -- an INTERVAL DAY(6) TO FRACTION(3)
'7623-11' -- an INTERVAL YEAR(9) TO MONTH
INTERVAL(18734:45) HOUR(5) TO MINUTE
INTERVAL(-7634-11) YEAR(5) TO MONTH
```

## PostgreSQL

PostgreSQL provides an `INTERVAL`

data type which is equivalent to the Informix `INTERVAL`

:

- It is possible to specify the interval class / precision with
`YEAR`

,`MONTH`

,`DAY`

,`HOUR`

,`MINUTE`

and`SECOND[(p)]`

fields. - Fractional part of seconds can be defined with up to 6 digits.
- The interval value range is from -178000000 to +178000000 years.
- Input and output format can be controlled with the
`SET interval style`

command.

## Solution

Use the following conversion rules to map Informix numeric types to PostgreSQL numeric types:

Informix data type | PostgreSQL data type |
---|---|

`INTERVAL YEAR[(p)] TO MONTH` |
`INTERVAL YEAR TO MONTH` |

`INTERVAL YEAR[(p)] TO YEAR` |
`INTERVAL YEAR` |

`INTERVAL MONTH[(p)] TO MONTH` |
`INTERVAL MONTH` |

`INTERVAL DAY[(p)] TO FRACTION(n)` |
`INTERVAL DAY TO SECOND(n)` |

`INTERVAL DAY[(p)] TO SECOND` |
`INTERVAL DAY TO SECOND(0)` |

`INTERVAL DAY[(p)] TO MINUTE` |
`INTERVAL DAY TO MINUTE` |

`INTERVAL DAY[(p)] TO HOUR` |
`INTERVAL DAY TO HOUR` |

`INTERVAL DAY[(p)] TO DAY` |
`INTERVAL DAY` |

`INTERVAL HOUR[(p)] TO FRACTION(n)` |
`INTERVAL HOUR TO SECOND(n)` |

`INTERVAL HOUR[(p)] TO SECOND` |
`INTERVAL HOUR TO SECOND(0)` |

`INTERVAL HOUR[(p)] TO MINUTE` |
`INTERVAL HOUR TO MINUTE` |

`INTERVAL HOUR[(p)] TO HOUR` |
`INTERVAL HOUR` |

`INTERVAL MINUTE[(p)] TO FRACTION(n)` |
`INTERVAL MINUTE TO SECOND(n)` |

`INTERVAL MINUTE[(p)] TO SECOND` |
`INTERVAL MINUTE TO SECOND(0)` |

`INTERVAL MINUTE[(p)] TO MINUTE` |
`INTERVAL MINUTE` |

`INTERVAL SECOND[(p)] TO FRACTION(n)` |
`INTERVAL SECOND(n)` |

`INTERVAL SECOND[(p)] TO SECOND` |
`INTERVAL SECOND(0)` |

`INTERVAL FRACTION TO FRACTION(n)` |
`INTERVAL SECOND(n)` |

The PostgreSQL database interface converts the Informix-style `INTERVAL`

type to
the native PostgreSQL `INTERVAL`

type.

Important: The PostgreSQL database driver forces the interval style session parameter to
'iso_8601', this is required to insert and fetch interval database with the libpq CAPI
functions. You must not change this setting during program execution.

While PostgreSQL intervals support up to 9 digits for the higher unit like Informix, year values range from -178000000 to +178000000 only. This limitation exists in PostgreSQL 8.4 and maybe solved in future versions.

The

`INTERVAL`

types translation can be controlled with the following FGLPROFILE
entry:`dbi.database.``dsname`.ifxemul.datatype.interval = __{__ true __|__ false __}__

For more details see IBM Informix emulation parameters in FGLPROFILE.