Instert rows into a database with a timestamp

Hello all!

I want to insert previously calculated values into a database. Ideally it would have just two columns, the timestamp and the value. I want the first column to be the timestamp so it’s easier to query afterwards periods of time (if there is a better way to accomplish this, please let me know).

The problem is that if I select DataType.DateTime as the column type, it won’t accept java.util.Date . To what type should I transform my timestamp?

This is what I have so far:
In a runnable:

                SimpleDateFormat format = new SimpleDateFormat("yyyy_MM");
                String date = format.format(Date.from(;
                String tableName = date + params.getTagDataTableName();

                checkTableStructure(tableName + "_A");
                checkTableStructure(tableName + "_B");
                checkTableStructure(tableName + "_C");
                BasicHistoricalRecord aRecord = new BasicHistoricalRecord(tableName + "_A", "Historization");
                BasicHistoricalRecord bRecord = new BasicHistoricalRecord(tableName + "_B", "Historization");
                BasicHistoricalRecord cRecord = new BasicHistoricalRecord(tableName + "_C", "Historization");

                addData(aRecord, aMean);
                addData(bRecord, bMean);
                addData(cRecord, cMean);

                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), aRecord);
                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), bRecord);
                GatewayHook.getGatewayContext().getHistoryManager().storeHistory(params.getDataSource(), cRecord);

To check the table structure/create it:

private void checkTableStructure(String tableName) {
        SRConnection con = null; 
            con = GatewayHook.getGatewayContext().getDatasourceManager().getConnection(params.getDataSource());
            DBTableSchema table = new DBTableSchema(tableName,con.getParentDatasource().getTranslator());
            table.addRequiredColumn("Timestamp", DataType.DateTime, null);
            table.addRequiredColumn("Value", DataType.Float4, null);
        }catch(Exception e){
  "Exception checking table structure");

And to add the values to the records:

private void addData(BasicHistoricalRecord record, float value){
        List<Object> timeValue= new ArrayList<>();
        try {
        } catch (Exception e) {


As it is right now, it is getting this exception:

java.lang.RuntimeException: java.sql.BatchUpdateException: Batch entry 0 INSERT INTO 2021_04data_table_temp("Timestamp","Value") VALUES('2021-04-14 09:51:14.384+02',321.11627) was aborted: ERROR: syntax error at or near "2021" Position: 13 Call getNextException to see other errors in the batch

If you think there is a better way to do this please let me know :slight_smile: . Keeping in mind that this same module would query through this database that I’m creating (ideally all the values between two dates), would I be able to use any methods from Ignition’s libraries? Which ones?

Thank you in advance for your help!

That’s pretty odd; it should be using parameters correctly when inserting your values into the DB…
If you put the gateway.database.updates on TRACE, is the query being executed correctly using SQL placeholders?

Apparently table names shouldn’t start with a number!

1 Like