Averaging over time period and grouping by properties

Hi swarnim! What queries have you tried? I'm not sure I'm fully following.

If your timestamp is stored as a string within the database rather than a date-like type then I'd only see one option. Query all results within the range, convert your timestamp into a date post-query and then group by the needed date frequency. I guess, alternatively, you could also try to group by the left portion of the string consisting of the date.

If it is a date-like type then you should be able to use groupByRaw to group & sum by the needed date frequency. How that looks will vary depending on your database (MySQL, PostgreSQL, etc). https://knexjs.org/guide/query-builder.html#groupbyraw

// backend/app/models/genset_property.ts

export default class GensetProperty extends BaseModel {

@column({ isPrimary: true })

declare id: number;

@column()

declare propertyName: string;

@column()

declare readablePropertyName: string;

@column()

declare physicalQuantityId: number;

@belongsTo(() => PhysicalQuantity)

declare physicalQuantity: BelongsTo<typeof PhysicalQuantity>;

}

I'll attach the models for your reference:

// backend/app/models/archive.ts

export default class Archive extends BaseModel {

@column({ isPrimary: true })

declare id: number;

@column()

declare timestamp: DateTime;

@column()

declare gensetPropertyId: number; //

@belongsTo(() => GensetProperty)

declare gensetProperty: BelongsTo<typeof GensetProperty>;

@column()

declare propertyValue: number;

@column()

declare isAnomaly: boolean;

}