Posted 1 month ago by
swarnim (@swarnim)

Averaging over time period and grouping by properties

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.

Join The Discussion! (3 Replies)

Please sign in or sign up for free to join in on the dicussion.

  1. Commented 1 month ago

    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;

    }

    0

    Please sign in or sign up for free to reply

  2. Commented 1 month ago


    // 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>;

    }

    0

    Please sign in or sign up for free to reply

  3. Commented 1 month ago

    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

    0

    Please sign in or sign up for free to reply