比较会议室数据库中的日期 [英] Comparing a date in a Room database

问题描述

我有这个Entry数据类

I have this Entry data class

@Entity(tableName = "entry")
@Typeconverters(DateConverter::class)
data class Entry(

    @PrimaryKey(autoGenerate = false)
    var id : String,
    var username : String,
    var type : String,
    var description : String,
    var category : String,
    var amount : Double,
    var date : String,
    var lastUpdate : String,
    var isDeleted : Boolean)
}

日期字段包含表示"yyyy-MM-dd"中日期的字符串.格式，而lastUpdate包含一个字符串，该字符串表示"yyyy-MM-dd hh:mm:ss"中的日期格式.如果我将这些变量存储为字符串，则由于Room不支持SQL的DATE()和DATETIME()数据类型，因此无法对它们进行SQL比较，因此查询如下:

The date field contains a string that represents a date in the "yyyy-MM-dd" format, while the lastUpdate contains a string that represents a date in the "yyyy-MM-dd hh:mm:ss" format. If i store those variables as strings i cannot do SQL comparisons on them since Room does not support SQL's DATE() and DATETIME() datatype and thus queries like this:

@Query(SELECT * FROM entry WHERE date >= :fromDate AND date <= :untilDate)

将无法正常工作.有什么办法可以解决这个问题?

Will not work properly. Is there any way to fix this?

推荐答案

好吧，我看到3个选项.

Well, I see 3 options.

1. 由于日期字符串以一种很好的分层方式(年，月，日)格式化，因此您实际上应该能够使用其自然的String排序.

1. Since your date string is formatted in a nice hierarchical way (year, month, day), you should actually be able to use its natural String sort.

如果需要在SQL查询中进行真实日期排序，则必须将日期另存为真实日期字段或整数字段(Unix时代时间戳记)

If you need real date sort within a SQL query, you'll have to save your date as real date-field or integer field (Unix epoch timestamp)

如果可以从数据库中获取日期之后或在将其存储到数据库中之前对日期进行排序，请熟悉Room中的TypeAdapter.这是一个简单的转换类，您可以在其中将String从String转换为DateTime并返回.

If it is okay to sort the date after fetching it from the DB or before storing it in the DB, make yourself familiar with TypeAdapter in Room. It's a simple conversion class where you can convert from String to DateTime and back.

回答您的第二个问题，为什么这样常见"?现成的类型不支持数据类型，我可以推荐这篇中等文章 :

To answer your second question on why such "common" data type is not supported out-of-the box, I can recommend this medium article:

SQLite是一种松散类型的数据库系统，并将所有值存储为一个 的值:NULL，INTEGER，TEXT，REAL或BLOB.您会注意到没有 特殊的日期或时间类型，就像您在其他数据库系统中可能会看到的一样.

SQLite is a loosely typed database system and stores all values as one of: NULL, INTEGER, TEXT, REAL or BLOB. You’ll notice that there is no special date or time type like you may find in other database systems.

相反，他们提供了有关如何存储的以下文档 日期/时间值:SQLite没有为它预留存储类 存储日期和/或时间.而是内置的日期和时间 SQLite的功能能够将日期和时间存储为TEXT， 实数或整数值

Instead they provides the following documentation on how to store date/time values: SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values

如果您进一步考虑，就会出现问题:什么是通用数据类型?结尾.当然，他们可以提供一些TypeConverters，但另一方面，每种数据类型则需要几行代码.

If you think about it further, the question arises: What is a common data type and where does "common" end. Of course, they could provide some TypeConverters, but on the other hand it's a few lines of code for each data type.

以下是TypeConverter从日期到字符串再返回的示例:

Here is an example for a TypeConverter from Date to String and back:

public class Converters {
  @TypeConverter
  public static Date fromTimestamp(Long value) {
    return value == null ? null : new Date(value);
  }

  @TypeConverter
  public static Long dateToTimestamp(Date date) {
    return date == null ? null : date.getTime();
  }
}

这篇关于比较会议室数据库中的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！