CODEWITHSUNDEEP
postgresqlintervalspostgresql-8.3
Leonard
Leonard

Reputation: 458

Postgresql 8.3 doesn't understand negative interval greater than a day

The following statements are understood by Postgresql 8.3:

SELECT '1.12:00:00'::interval
SELECT '12:00:00'::interval
SELECT '-12:00:00'::interval

But the following statement isn't understood:

SELECT '-1.12:00:00'::interval

I receive the following error:

ERROR:  invalid input syntax for type interval: "-1.00:02:00"

********** Error **********

ERROR: invalid input syntax for type interval: "-1.00:02:00"
SQL state: 22007

How can I work with larger negative intervals?

Upvotes: 3

Views: 3210

Answers (2)

Leonard
Leonard

Reputation: 458

Based on reactions to my question and my own investigation, the problem turned out to be a mix of usage of two types: .NET's TimeSpan and NpgsqlTypes.NpgsqlInterval. I have changed everything to NpgsqlInterval which solved my problem.

I can now work with larger negative values for intervals because of omitting the usage of the TimeSpan type. When the interval becomes larger than a day (negative), TimeSpan and Interval work differently so mixing these types isn't a good idea in this case.

Upvotes: 0

rchang
rchang

Reputation: 5236

Based on the documentation for intervals, I think the correct notation would be:

SELECT '-1 -12:00:00'::interval;  -- Assuming you want an interval equivalent to -36 hours

postgres=# select '-36 hours'::interval = '-1 -12:00:00'::interval;
 ?column? 
----------
 t
(1 row)

Incidentally, it's possible that your notation of 1.12:00:00 isn't doing what it's intended - my interpretation is that you're trying to represent 36 hours (1 day + 12 hours) - due to the same notation issue.

postgres=# select
postgres-#   '1.12:00:00'::interval = '36 hours'::interval,
postgres-#   '1 12:00:00'::interval = '36 hours'::interval;
 ?column? | ?column? 
----------+----------
 f        | t
(1 row)

Upvotes: 4

Related Questions