Does OmniDB auto-convert intervals to days?

[cschwaderer]

I've noticed that OmniDB 2.8 shows a strange behavior when displaying intervals. Running this query SELECT age('2018-06-16'::date,'2018-05-01'); results in psql, http://sqlfiddle.com, and pgAdmin 3 in 1 mon 15 days. However, OmniDB returns 45 days, 0:00:00 - so, only days, no month.
Is this a feature? Does OmniDB do some auto-conversion? If yes: I'd like to be able to turn it off, as it confuses me when it comes to months with 30 days vs 31 days.

[wind39]

Hi @cschwaderer ,

Thanks for reporting this. OmniDB has a generic database layer called Spartacus, which uses specific Python drivers for handling connection to specific RDBMS, such as PostgreSQL (psycopg2), Oracle (cx_Oracle), and so on.

So I tested your example in Spartacus:

>>> import Spartacus.Database
>>> d = Spartacus.Database.PostgreSQL('127.0.0.1', 5432, 'william', 'william', '')
>>> d.Open()
>>> x = d.ExecuteScalar("SELECT age('2018-06-16'::date,'2018-05-01')")
>>> x
datetime.timedelta(45)
>>> d.Close()

Notice how the PostgreSQL INTERVAL is converted to Python datetime.timedelta(45), which does not support months and years. The solution is explained in this psycopg2 issue. After applying it to Spartacus' source code, I'm able to get the desired output:

>>> import Spartacus.Database
>>> d = Spartacus.Database.PostgreSQL('127.0.0.1', 5432, 'william', 'william', '')
>>> d.Open()
>>> x = d.ExecuteScalar("SELECT age('2018-06-16'::date,'2018-05-01')")
>>> x
'1 mon 15 days'
>>> d.Close()

I already included this fix in OmniDB dev branch, which will become release 2.9 in June, 14th. We will keep this issue open so I can notify you about the new release, then you can test it and give us your valuable feedback, if you want.

Thanks and regards,
William Ivanski

[cschwaderer]

Thanks! Works now in 2.9!