如何存储日期范围（实际上是时间戳）

Question

Java& Oracle 都有一个名为 Date 的时间戳类型。 开发人员倾向于将这些日期视为日历日期，我已经看到这会导致令人讨厌的一次性错误。

1. 对于基本日期量，您可以简单地在输入时截掉时间部分，即降低精度。 但如果您使用日期范围（例如：9/29-9/30）执行此操作，则这两个值之间的差异为 1 天，而不是 2 天。此外，范围比较需要 1 ) 截断操作： start < trunc(now) <= end，或 2) 算术：start < 现在< （结束 + 24 小时）。 不可怕，但也不是干。

2. 另一种方法是使用真实时间戳：9/29 00:00:00 - 10/1 00:00:00。 （午夜到午夜，因此不包括十月的任何部分）。 现在，持续时间本质上是正确的，并且范围比较更简单：start <= now start <= now start <= now start <= now start <= now start <= now start <= now start 结束。 对于内部处理来说当然更干净，但是结束日期确实需要在初始输入 (+1) 和输出 (-1) 时进行转换，假设用户级别的日历日期隐喻。

您如何处理项目的日期范围？ 还有其他选择吗？ 我特别感兴趣的是您如何在 Java 和 Oracle 方面处理这个问题。

Answer 1

我们是这样做的。

1. 使用时间戳。

2. 使用半开区间进行比较：start <= now < end。

忽略那些坚持认为 BETWEEN 在某种程度上对于成功的 SQL 至关重要的抱怨者。

有了这个一系列的日期范围就很容易审核。 9/30 到 10/1 的数据库值包含一天 (9/30)。 下一个间隔的开始必须等于前一个间隔的结束。 interval[n-1].end == Interval[n].start 规则对于审核来说很方便。

显示时，如果愿意，可以显示格式化的start和end-1。 事实证明，你可以教育人们理解“结束”实际上是规则不再成立的第一天。 所以“9/30 to 10/1”的意思是“从9/30开始有效，从10/1开始不再有效”。

Answer 2

Oracle 具有 TIMESTAMP 数据类型。 它存储 DATE 数据类型的年、月和日，以及小时、分钟、秒和小数秒值。

这是一个 asktom 上的线程。 oracle.com 有关日期算术的信息。

Answer 3

我赞同 S.Lott 的解释。 我们有一个产品套件，它广泛使用日期时间范围，这是我们处理此类范围的经验教训之一。 顺便说一句，如果结束日期不再是范围的一部分（IOW，半开区间），我们将其称为独占结束日期。 相反，如果它算作范围的一部分，则它是一个包含的结束日期，只有在没有时间部分的情况下才有意义。

用户通常期望包含日期范围的输入/输出。 无论如何，应尽快将用户输入转换为独占结束日期范围，并在必须向用户显示时尽可能晚地转换任何日期范围。

在数据库中，始终存储排他的结束日期范围。 如果存在具有包含结束日期范围的旧数据，请尽可能将它们迁移到数据库上，或者在读取数据时尽快转换为不包含结束日期范围。

Answer 4

我使用 Oracle 的日期数据类型，并向开发人员介绍影响边界条件的时间分量问题。

数据库约束还可以防止在不应包含时间分量的列中意外指定时间分量，并且还告诉优化器所有值都没有时间分量。

例如，约束 CHECK (MY_DATE=TRUNC(MY_DATE)) 可防止将时间不是 00:00:00 的值放入 my_date 列中，并且还允许 Oracle 推断诸如 MY_DATE = TO_DATE(' 2008-09-12 15:00:00') 永远不会为 true，因此不会从表中返回任何行，因为它可以扩展为：

MY_DATE = TO_DATE('2008-09-12 15:00:00') AND
TO_DATE('2008-09-12 15:00:00') = TRUNC(TO_DATE('2008-09-12 15:00:00'))

这当然自动为 false。

尽管有时很容易将日期存储为数字（例如 20080915），但这可能会导致查询优化问题。 例如，20,071,231 和 20,070,101 之间有多少个合法值？ 2007 年 12 月 31 日至 2008 年 1 月 1 日之间怎么样？ 它还允许输入非法值，例如 20070100。

因此，如果您的日期没有时间部分，则定义范围变得很容易：

select ...
from   ...
where  my_date Between date '2008-01-01' and date '2008-01-05'

当有时间部分时，您可以执行以下操作之一：

select ...
from   ...
where  my_date >= date '2008-01-01' and
       my_date  < date '2008-01-06'

或

select ...
from   ...
where  my_date Between date '2008-01-01'
                   and date '2008-01-05'-(1/24/60/60)

注意 (1 /24/60/60) 而不是幻数。 在 Oracle 中，通过添加一天的定义小数部分来执行日期算术是很常见的……3/24 表示三个小时，27/24/60 表示 27 分钟。 这种类型的 Oracle 数学是精确的，不会出现舍入错误，因此：

select 27/24/60 from dual;

... 给出 0.01875，而不是 0.01874999999999 或其他值。

Answer 5

我还没有看到发布的间隔数据类型。

Oracle 还提供适合您具体场景的数据类型。 Oracle 中也有 INTERVAL YEAR TO MONTH 和 INTERVAL DAY TO SECOND 数据类型。

来自 10gR2 文档。

INTERVAL YEAR TO MONTH 存储一个周期
使用 YEAR 和 MONTH 的时间
日期时间字段。 该数据类型是
对于表示差异很有用
仅当两个日期时间值之间
年和月的值是
意义重大。

间隔年 [(year_ precision)] TO
月

其中year_ precision 是
YEAR 日期时间字段中的数字。 这
year_ precision的默认值为2。

INTERVAL DAY TO SECOND 数据类型

INTERVAL DAY TO SECOND 存储一个句点
以天、小时为单位的时间，
分钟和秒。 该数据类型是
对于表示精确的有用
两个日期时间之间的差异
值。

按如下方式指定此数据类型：

间隔天 [(day_ precision)] 至
第二
[(fractional_seconds_ precision)]

哪里

day_ precision 是位数
在 DAY 日期时间字段中。 公认
值为 0 到 9。默认值为 2。

fractional_seconds_ precision 是
小数的位数
第二个日期时间字段的一部分。
接受的值为 0 到 9。
默认值为 6。

你有很大的灵活性
当将间隔值指定为
文字。 请参阅“间隔
文字”以获取详细信息
将间隔值指定为文字。
另请参阅“日期时间和间隔
示例”的示例使用
间隔。

Answer 6

根据我的经验，主要有四种方法：

1）将日期转换为纪元整数（自 1970 年 1 月 1 日以来的秒数）并将其作为整数存储在数据库中。

2）将日期转换为YYYYMMDDHHMMSS整数，并将其作为整数存储在数据库中。

3) 将其存储为日期

4) 将其存储为字符串

我一直坚持使用 1 和 2，因为它使您能够对日期执行快速而简单的算术，而不依赖于底层数据库功能。

Answer 7

根据您的第一句话，您偶然发现了 Java 的一个隐藏“功能”（即错误）：java.util.Date 应该是不可变的，但事实并非如此。 （Java 7 承诺通过新的日期/时间 API 来解决此问题。）几乎每个企业应用程序都依赖于各种 时间模式，并且在某些时候您将需要对日期和时间进行算术运算。

理想情况下，您可以使用 Google 日历使用的 Joda 时间。 如果你做不到这一点，我猜想一个 API 由一个围绕 java.util.Date 的包装器组成，其计算方法类似于 Grails/Rails，以及一系列包装器（即有序的对指示一个时间段的开始和结束）就足够了。

在我当前的项目（HR 计时应用程序）中，我们尝试将 Oracle 和 Java 的所有日期标准化为同一时区。 幸运的是，我们的本地化要求很轻（= 1 个时区就足够了）。 当持久对象不需要比一天更精细的精度时，我们使用午夜的时间戳。 我会更进一步，坚持将额外的毫秒丢弃到持久对象可以容忍的最粗粒度（这将使您的处理更简单）。

Answer 8

通过将 getTime() 的结果存储为长整数，所有日期都可以明确存储为 GMT 时间戳（即没有时区或夏令时问题）。

如果数据库查询中需要进行日、周、月等操作，并且当查询性能至关重要时，可以将时间戳（标准化为比毫秒更高的粒度）链接到包含当天列的日期细分表、周、月等值，以便在查询中不必使用昂贵的日期/时间函数。

Answer 9

艾伦是对的——乔达时间很棒。 java.util.Date 和 Calendar 真是太可惜了。

如果您需要时间戳，请使用带有时间的 Oracle 日期类型，请使用某种后缀（如 _tmst）命名该列。 当您将数据读入java时，将其放入joda时间DateTime对象中。 为了确保时区正确，请考虑 oracle 中存在特定的数据类型，它们将存储带有时区的时间戳。 或者您可以在表中创建另一列来存储时区 ID。 时区 ID 的值应该是时区的标准全名 ID，请参阅 http://java.sun.com/j2se/1.4.2/docs/api/java/util/TimeZone.html#getTimeZone%28java.lang.String% 29.. 如果您对 TZ dta 使用另一列，那么当您将数据读入 java 时，请使用 DateTime 对象，但使用 .withZoneRetainFields 设置时区。

如果您只需要日期数据（无时间戳），则使用数据库中不带时间的日期类型。 再次命名好。 在本例中，使用 jodatime 中的 DateMidnight 对象。

底线：利用数据库的类型系统和您正在使用的语言。 学习它们并获得具有表现力的 API 和语言语法来处理您的问题的好处。

Answer 10

更新：Joda-Time 项目现在处于维护模式。 其团队建议迁移到 Java 内置的 java.time 类。

Joda-Time

Joda-Time 提供 3 个类来表示时间跨度：Interval、Duration 和 period。

ISO 8601 标准指定如何格式化表示 Duration 和 间隔。 Joda-Time 解析并生成此类字符串。

时区是一个重要的考虑因素。 您的数据库应以 UTC 格式存储其日期时间值。 但您的业务逻辑可能需要考虑时区。 “一天”的开始取决于时区。 顺便说一句，请使用正确的时区名称而不是 3 或 4 个字母的代码。

S.Lott 的正确答案明智地建议使用半开放逻辑，因为这通常最适合日期时间工作。 时间跨度的开始是包含，而结束是排除。 Joda-Time 在其方法中使用半开放逻辑。

DateTimeZone timeZone_NewYork = DateTimeZone.forID( "America/New_York" );
DateTime start = new DateTime( 2014, 9, 29, 15, 16, 17, timeZone_NewYork );
DateTime stop = new DateTime( 2014, 9, 30, 1, 2, 3, timeZone_NewYork );

int daysBetween = Days.daysBetween( start, stop ).getDays();

Period period = new Period( start, stop );

Interval interval = new Interval( start, stop );
Interval intervalWholeDays = new Interval( start.withTimeAtStartOfDay(), stop.plusDays( 1 ).withTimeAtStartOfDay() );

DateTime lateNight29th = new DateTime( 2014, 9, 29, 23, 0, 0, timeZone_NewYork );
boolean containsLateNight29th = interval.contains( lateNight29th );

转储到控制台...

System.out.println( "start: " + start );
System.out.println( "stop: " + stop );
System.out.println( "daysBetween: " + daysBetween );
System.out.println( "period: " + period ); // Uses format: PnYnMnDTnHnMnS
System.out.println( "interval: " + interval );
System.out.println( "intervalWholeDays: " + intervalWholeDays );
System.out.println( "lateNight29th: " + lateNight29th );
System.out.println( "containsLateNight29th: " + containsLateNight29th );

运行时...

start: 2014-09-29T15:16:17.000-04:00
stop: 2014-09-30T01:02:03.000-04:00
daysBetween: 0
period: PT9H45M46S
interval: 2014-09-29T15:16:17.000-04:00/2014-09-30T01:02:03.000-04:00
intervalWholeDays: 2014-09-29T00:00:00.000-04:00/2014-10-01T00:00:00.000-04:00
lateNight29th: 2014-09-29T23:00:00.000-04:00
containsLateNight29th: true

Answer 11

我以毫秒为单位存储所有日期。 我根本不使用时间戳/日期时间字段。

所以，我必须尽可能地操纵它。 这意味着我在 sql 查询中不使用“before”、“after”、“now”关键字。