Unable to run between query Because number field in schema treated as string in SQLite DB

[pankajnegi1893]

I am facing an issue where I want to run between query between two range timestamp values.But in SQLite DB number value saved as string. because of this unable to run Between query.

class SomeTable extends Model {
  static table = 'some_table';

  @field('created') created!: string;

  @field('created_timestamp') createdTimeStamp!: number; // this we are in saving seconds == millisecond/1000
  ... some other fields

 updateMessageRecord(data:  any) {
    this.created = data.created || this.created;
    this.createdTimeStamp = parseInt(
      `${new Date(data.created || this.created).getTime() / 1000}`,
      10,
    );
  
    return this;
  }
}

schema.ts

export default appSchema({
  version: 1,
 tables: [
 tableSchema({
      name: 'some_table',
      columns: [
        {name: 'created', type: 'string'},
        {name: 'created_timestamp', type: 'number'},
      ],
    }),
]
}

export const someTableCollection: Collection<SomeTable> =
  database.collections.get("some_table");

const someTableData = await someTableCollection
      .query(
        Q.where(
          'created_timestamp',
          Q.between(startingTimeStamp, endingTimeStamp),
        ),
        Q.sortBy('created_timestamp', Q.desc),
      )
      .fetch();

In DB created_timestamp saved as string. it should be number type.

is Any Issue with WatermelonDB to not taking number ?

version used

"@nozbe/watermelondb": "^0.27.1",
"@nozbe/with-observables": "^1.6.0",
"react-native": "0.72.5",
 "react": "18.2.0",

[pankajnegi1893]

in place of created_timestamp I have used 'created_timestamp_at' then it's working

https://watermelondb.dev/docs/Schema#naming-conventions

[garygcchiu]

Is there a reason you couldn't have used the automatic created_at? https://watermelondb.dev/docs/Advanced/CreateUpdateTracking