How can I write data of type INTERVAL to PostgreSQL database?

Ignition
,
1

Hello, @PGriffith
I've searched for an answer to this question but it seems nobody has asked it.

How do you write time interval data to a Postgres database column of type INTERVAL using Ignition scripting?
image

In scripting, I would like to run a command like this:

import datetime
runtime = datetime.timedelta(seconds=300)
databaseConnection = "my_DB"
SQL = 'INSERT INTO example_db (runtime)  VALUES (?);'
system.db.runPrepUpdate(sql, (runtime,), databaseConnection)

In my experience with Python 3.X, this should work fine.

However, it always returns an unhelpful error message:

postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.python.core.PyObjectDerived. Use setObject() with an explicit Types value to specify the type to use.
at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1009)

What does this error message mean?
And how can I write timedelta data to Postgres? Is there some kind of limitation with Jython here?

Thank you

2

EDIT: DOH! Forgot to welcome you to the forums... sorry 'bout that...

Intervals are not a standard JDBC data type.

db = 'Test'

seconds = 300

query = "INSERT INTO interval_table (runtime) VALUES (?::interval)"

system.db.runPrepUpdate(query, ['{} SECONDS'.format(seconds)], db)
2 Likes