3

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?

Leonard
  • 348
  • 1
  • 6
  • 20
  • Why do you think an interval can accept a dot to separate date and time in its [input](http://www.postgresql.org/docs/current/static/datatype-datetime.html#DATATYPE-INTERVAL-INPUT)? – pozs Feb 03 '15 at 16:15
  • Wow, it seems `interval '1.12:00:00'` is parsed as `interval '1 day 02:52:48'`, so you should read that as *1.12 days* (which is odd, but you shouldn't rely on this). – pozs Feb 03 '15 at 16:22
  • Version 8.3 is EOL since February 2013. Do yourself a favor and upgrade to a recent version: 8.3 has several serious (security) bugs. CVE-2013-1899 is probably the worst. – Frank Heikens Feb 03 '15 at 18:02
  • The upgrade issue is a whole other thing which might happen next summer or won't ever happen due to a migration to Sql Database (Azure). – Leonard Feb 03 '15 at 21:25
  • Thanks for your inputs. The dot-notation was given to me by my simple c#-app that converts a .net-timespan to a postgres-interval. I haven't yet tested my routine on intervals more than a day, but I'll do so tomorrow. – Leonard Feb 03 '15 at 21:31
  • It occurs to me that the .net TimeSpan works another way than Postgres considering intervals more than 24 hours. I'll try to make a decent INTERVAL out of the TimeSpan. – Leonard Feb 04 '15 at 07:53

2 Answers2

4

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)
rchang
  • 5,150
  • 1
  • 15
  • 25
0

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.

Leonard
  • 348
  • 1
  • 6
  • 20