So i have a adonis model where i store propertyValue and each entry has a timestamp (ISO 8601). There are several properties which is related via a propertyId.
So my objective is to aggregate data and get averages over a day for a particular property and show it in a graph.
Similarly I also need to average over a week, when a month's graph has to be shown.
I looked into aggregates and avg but im not able to achieve what i expect.
- Posted 2 months ago by
- swarnim (@swarnim)

Join The Discussion! (3 Replies)
Please sign in or sign up for free to join in on the dicussion.
swarnim
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;
}
Please sign in or sign up for free to reply
swarnim
// 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>;
}
Please sign in or sign up for free to reply
tomgobich
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#groupbyrawPlease sign in or sign up for free to reply