Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website

SQL Datasource time override


  • Hi all,
    we are testing the SQL datasource (column-based query) to acquire continuosly data from a third party supervision systems; we need to use the "Time override column" in order to import into Mango database the correct timestamp of each sample.
    All works perfectly for all configured datapoints, except for a particular table in which the record's time is stored as BIGINT instead of TIMESTAMP (e.g. 20181001163900 instead of "2018-10-01 16:39:00").
    Is there a way to acquire this data with the their correct timestamp?

    Thanks.
    Ste


  • Hi Ste, welcome to the forum!

    Have you attempted it? The function getting the time (converting to an epoch timestamp) for points using the time override column looks like this currently:

        private long getTimeOverride(ResultSetMetaData meta, int column, ResultSet rs, long time) throws SQLException {
            switch (meta.getColumnType(column)) {
            case Types.BIGINT:
            case Types.LONGVARCHAR:
                return rs.getLong(column);
            case Types.DATE:
                return rs.getDate(column).getTime();
            case Types.TIME:
                return rs.getTime(column).getTime();
            case Types.TIMESTAMP:
                return rs.getTimestamp(column).getTime();
            }
    
            raiseEvent(STATEMENT_EXCEPTION_EVENT, time, true, new TranslatableMessage("event.sql.dataTypeNotSupported",
                    meta.getColumnTypeName(column), meta.getColumnType(column)));
            return -1;
        }
    

    If the dates are mistaken or it's not working quite as you expect you could try adding some convert function(s) into your select statement.


  • @phildunlap
    Hi Philip,
    convert functions into my select statement work!

    Thanks
    Ste