Timestamp for integers

Question

I have a problem with a pgsql query that throws "ERROR: integer out of range".

What I'm trying to do is check if the date containing the valid_to field (like "timestamp without timezone") is specified exactly (N * interval) except for the current date. In the failed request example, I use "7776000" as the interval (90 days) and "1399327200" as the current date in unix time.

What works:

SELECT 
    *,
    (CAST((EXTRACT(EPOCH FROM et.valid_to) - 1399327200 ) as integer) % 7776000) as modulo
FROM example_table et 
WHERE et.valid_to IS NOT NULL

What not:

WITH table_refined as (
    SELECT 
        *,
        (CAST((EXTRACT(EPOCH FROM et.valid_to) - 1399327200 ) as integer) % 7776000) as modulo
    FROM example_table et 
    WHERE et.valid_to IS NOT NULL 
)
SELECT * from table_refined WHERE modulo=0

And this:

SELECT * FROM (
    SELECT 
        *,
        (CAST((EXTRACT(EPOCH FROM et.valid_to) - 1399327200 ) as integer) % 7776000) as modulo
    FROM example_table et
    WHERE et.valid_to IS NOT NULL 
)
AS table_temp 
WHERE table_temp.modulo = 0

Since I first subtract the current date from timestamp, then the value, which is the castet for integer, should not be outside the integer bounds. Of course, there are a few NULLs, but as the first request goes fine, this is probably not a problem.

Answer 1

, - "valid_to" "2050-01-01", , , .

AND valid_to < '2100-01-01'

, , , . unix 2038 , , postgresql casting , 2100 .

Answer 2

, ,

select *,
    valid_to,
    valid_to - currrent_date - 90 * interval '1 day'
from et
where
    valid_to is not null
    and
    valid_to - currrent_date = 90 * interval '1 day'