How Does Fivetran Convert PostgreSQL interval Columns?

Question

Different source interval values sometimes appear as the same floating-point value in the destination. How does Fivetran convert PostgreSQL interval columns?

Environment

Connector: PostgreSQL

Answer

Fivetran maps PostgreSQL internal values to Fivetran DOUBLE. For more information, see Type transformation and mapping.

PostgreSQL stores interval values as three components: months, days, and microseconds. For example:

interval '1 year'      -- stored as 12 months
interval '12 months'   -- stored as 12 months
interval '365 days'    -- stored as 365 days, not 12 months

When we map interval values to DOUBLE, we convert the interval into total seconds. Therefore, different source values can produce the same destination value if they convert to the same number of seconds.

For example, interval '1 year' and interval '12 months' produce the same value because PostgreSQL stores both as 12 months. However, interval '365 days' is stored differently and shouldn't be described as equivalent to 1 year or 12 months at the PostgreSQL storage level.