CODEWITHSUNDEEP
sqlpostgresql
Alex
Alex

Reputation: 51

Casting timestamp to integer issues

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

What I am trying to accomplish is to check if date contained valid_to field (of 'timestamp without timezone' type) is exactly an (N * interval) apart from current date. In example of failing query I am using "7776000" as interval (90 days) and "1399327200" as 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 does 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

Nor 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

As I am first substracting current date from timestamp, then value that is castet to integer shouldn't be out of integer bounds. There are some NULLs of course, but as first query passes OK, it's rather not the problem.

Upvotes: 4

Views: 691

Answers (2)

Alex
Alex

Reputation: 51

I think I found the reason - among the records there were one with 'valid_to' field with '2050-01-01' date, and that one surely caused all this fuss. Therefore, I added

AND valid_to < '2100-01-01'

to my query what made it finally work. Although unix time is to reach integer limit in 2038, it looks like postgresql casting make it working up to the unsigned integer max value, so limiting records to year 2100 works.

Upvotes: 1

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125434

I can't see the error cause but you can avoid all that by using intervals

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'

Upvotes: 0

Related Questions