How to get sorted data from Room DB on the base of Date/TimeStamp?

Question

I have a class which is containing Data related to different teams. There is a column in db named as "LastMessageAt" and its data type is String. I am getting this field as String from API and I am storing it as String in Room Database. Now I want to get Sorted Teams Data from my Database, Sorting should be on the basis of "LastMessageAt" column. I am getting timestamp as string in the form of : "2022-07-18T12:12:40.223000Z"

How can I do this?

My Table class is:

@Entity(tableName = "CompanyTeam")
public class CompanyTeam implements Serializable  {
@PrimaryKey
@NonNull
@ColumnInfo(name = "TeamId")
private String teamId;

@ColumnInfo(name = "TeamName")
private String teamName;

@ColumnInfo(name = "LastMessageAt")
private String lastMessageAt;



@NonNull
public String getTeamId() {
    return teamId;
}

public void setTeamId(@NonNull String teamId) {
    this.teamId = teamId;
}

public String getTeamName() {
    return teamName;
}

public void setTeamName(String teamName) {
    this.teamName = teamName;
}

public String getCompanyId() {
    return companyId;
}

public void setCompanyId(String companyId) {
    this.companyId = companyId;
}

public String getLastMessageAt() {
    return lastMessageAt;
}

public void setLastMessageAt(String lastMessageAt) {
    this.lastMessageAt = lastMessageAt;
}

}

Answer 1

don’t store dates as strings, use a proper date datatype and write TypeConverters for your LastMessageAt like below :

class TimestampConverter {
    @TypeConverter
    fun fromTimestamp(value: Long?): Date? {
        return if (value == null) null else Date(value)
    }

    @TypeConverter
    fun dateToTimestamp(date: Date?): Long? {
        return date?.time
    }
}

and your model should look like this :

@NonNull
@ColumnInfo(name = "date")
@TypeConverters(TimestampConverter::class)
var lastMessageAt: Date? = null

then query like this :

@Query("SELECT * FROM CompanyTeam ORDER BY LastMessageAt DESC")