不要担心，保持快乐
Postgres如何对时间戳进行索引 - 换句话说，它会根据日期/时间对时间戳进行索引，还是会进入秒/毫秒等细节？
Postgres使用的索引方案的内部实现通常对您来说是透明的，无需关注。请记住，您今天学习的实现可能会在Postgres的未来版本中发生变化。
您可能会陷入过早优化的陷阱。在您知道自己存在可证明的性能问题之前，请相信Postgres及其默认行为。
时刻
日期时间处理比您想象的要复杂。
首先，您正在使用TIMESTAMP，它实际上是TIMESTAMP WITHOUT TIME ZONE的缩写名称。这种类型不能表示一个时刻。此类型仅存储日期和一天中的时间。例如，2020年1月23日中午12:00。但这是否意味着日本东京的中午？或者法国巴黎的中午，几个小时后？或者美国俄亥俄州托莱多市的中午，几个小时后？
我建议始终完整地展开类型名称，以便在SQL中非常清晰。使用TIMESTAMP WITHOUT TIME ZONE而不是TIMESTAMP。
但是，如果您实际上正在尝试表示某个时刻，即时间轴上的特定点，则必须使用
TIMESTAMP WITH TIME ZONE。这个名称来自SQL标准。但在Postgres和其他一些数据库中，它有点不准确。Postgres实际上并没有存储时区。相反，Postgres使用与输入一起提交的任何时区或UTC偏移信息进行调整到UTC。写入存储的值始终为UTC。如果您关心原始区域名称或偏移数字（小时-分钟-秒），则需要将其存储在第二列中。
从数据库检索时，该值也以UTC形式出现。但请注意，一些中间件工具坚持在检索后应用默认时区值。虽然出于善意，但这种反功能可能会引起很多混淆。使用如下所示的
java.time对象时，您将不会遇到此类混淆。
时间跨度查询
Postgres将一个时刻存储为UTC，可能作为
epoch-reference日期时间的计数，因为数据类型被记录为64位（8八位字节）的整数。根据维基百科，Postgres使用2000-01-01作为纪元参考，可能是该日期在UTC中的第一个时刻，即2000-01-01T00:00:00.0Z。我们没有任何理由关心使用的时代参考，但是您可以了解一下。
重点是，Postgres 中的日期时间值仅存储为数字，即
微秒计数。时间戳类型不是特定的日期和时间，正如您可能想象的那样。您的查询确实可以从时间戳列上的索引中受益，但不带时间的日期导向查询不会得到具体的好处。该索引不是面向日期的，也不能因为我将在下面解释而是这样的。
从瞬间确定日期需要时区。对于任何给定瞬间，日期因时区而变化。在法国巴黎午夜后几分钟是新的一天，而在魁北克蒙特利尔仍然是“昨天”。
要按日期查询瞬间，需要确定一天的第一个瞬间和随后一天的第一个瞬间。然后我们使用半开放方法来定义一个时间跨度，其中开始是包含的，而结束是排除的。我们搜索等于或晚于开始，同时位于结束之前的瞬间。提示：说“等于或晚于开始”的另一种方式是“不早于”。
您正在使用 Java，因此可以利用业界领先的java.time类。
Java.time 类使用比 Postgres 中使用的
纳秒更细的分辨率。因此，您将没有问题将 Postgres 值加载到 Java 中。但是，当进行反向操作时，请注意数据丢失，因为纳秒将被静默截断以仅存储微秒。
在确定一天的第一个时刻时，不要假定一天从00:00:00.0开始。某些区域的某些日期从另一个时间开始，例如01:00:00.0。始终让java.time确定一天的第一个时刻。
ZoneId z = ZoneId.of( "Asia/Tokyo" ) ; // Or `Africa/Tunis`, `America/Montreal`, etc.
LocalDate today = LocalDate.now( z ) ;
ZonedDateTime zdtStart = today.atStartOfDay( z ) ; // First moment of the day.
ZonedDateTime zdtStop = today.plusDays( 1 ).atStartOfDay( z ) ; // First moment of the following day.
请编写半开区间的SQL语句。不要使用SQL命令BETWEEN，因为它不是半开区间。
String sql = "SELECT * FROM tbl WHERE event !< ? && event < ? ;" ; // Half-Open query in SQL.
将起始和结束值传递给预处理语句。支持 J
DBC 4.2 及更高版本的
JDBC 驱动程序 可以使用
PreparedStatement::setObject 和
ResultSet::getObject 处理大多数
java.time。奇怪的是，JDBC 规范不需要支持两种最常用的类型：
Instant（始终为 UTC）和
ZonedDateTime。这些可能适用于特定驱动程序，也可能不适用。标准要求支持
OffsetDateTime，因此让我们转换为该类型。
preparedStatement.setObject( 1 , zdtStart.toOffsetDateTime() ) ;
preparedStatement.setObject( 2 , zdtStop.toOffsetDateTime() ) ;
传递给PreparedStatement的OffsetDateTime对象将携带该时区在该日期时间使用的偏移量。为了调试或好奇，您可能想在UTC中查看这些值。因此，让我们通过提取Instant并应用零小时-分钟-秒的偏移量来调整到UTC，以获得携带UTC本身偏移量的OffsetDateTime。
OffsetDateTime start = zdtStart.toInstant().atOffset( ZoneOffset.UTC ) ;
OffsetDateTime stop = zdtStop.toInstant().atOffset( ZoneOffset.UTC ) ;
传递给预处理语句。
preparedStatement.setObject( 1 , start ) ;
preparedStatement.setObject( 2 , stop ) ;
一旦这些
start和
stop值到达数据库服务器，它们将被转换为表示自纪元以来的计数的数字，一个简单的整数。然后，Postgres执行简单的数字比较。如果存在这些整数数字的索引，则该索引可能会根据Postgres查询规划器的需要使用或不使用。
如果您只有相对较少的行且有大量RAM可缓存它们，则可能不需要索引。进行测试，并使用EXPLAIN / ANALYZE查看实际性能。
通过Java的日期列
如果您已经完成了证明面向日期的查询存在性能问题的工作，可以添加第二个
DATE类型的列。然后对该列进行索引，并在面向日期的查询中明确引用它。
插入时，还应包含一个计算出的值，表示在适合您的应用程序的任何时区中感知日期。只需确保清楚地记录您的意图以及用于确定日期的时区的具体信息即可。提示：Postgres提供了一项功能，可以在列名和其数据类型旁边将文本片段作为列定义的一部分包含在内。
由于第二个
DATE列是从另一列派生出来的，因此它在定义上是冗余的并且去规范化了。通常情况下，您应该仅在最后一步考虑去规范化。
插入值时的Java代码。
String sql = "INSERT INTO tbl ( event , date_tokyo ) VALUES ( ? , ? ) ;" ;
确定当前时刻，并将当前时刻的日期视为在时区Asia/Tokyo中感知的日期。
Instant now = Instant.now() ; // Always in UTC, no need to specify a time zone here.
OffsetDateTime odt = now.atOffset( ZoneOffset.UTC ) ; // Convert from `Instant` to `OffsetDateTime` if your JDBC driver does not support `Instant`.
ZoneId z = ZoneId.of( "Asia/Tokyo" ) ;
ZonedDateTime zdt = now.atZone( z ) ;
LocalDate localDate = zdt.toLocalDate() ; // Extract the date as seen at this moment by people in the Tokyo time zone.
传递给你的预处理语句。
preparedStatement.setObject( 1 , odt ) ;
preparedStatement.setObject( 2 , localDate ) ;
现在你可以在
date_tokyo列上进行基于日期的查询。如有需要，请创建索引。
通过SQL使用日期列
或者，您可以在Postgres中自动填充date_tokyo列。
触发器
您可以编写一个触发器，使用内置于Postgres中的日期时间函数来确定该时刻在时区Asia/Tokyo中的日期。然后，触发器可以将结果日期值写入该第二列。
生成值列
或者，在Postgres 12中，您可以更简单地使用新的生成列功能。这个新功能可以完成相同的工作，但不需要定义和附加触发器。关于此新功能的讨论，请参见：
在Postgres 12中，具有GENERATED ALWAYS AS (…) STORED的列其值被物理存储，并且可以被索引。
注意事项
重要的是正确了解当前时区定义的信息，这对日期时间处理至关重要。通常，这些信息通过由
ICANN/IANA维护的
tz data获取。
Java和Postgres都包含自己的
tz data副本。
世界各地的政治家们经常喜欢重新定义他们的时区，往往没有或几乎没有提前警告。因此，请确保跟踪您关心的时区变化。当您更新Java或Postgres时，您可能会得到最新的tz数据副本。但在某些情况下，您可能需要手动更新Java和Postgres环境中的一个或两个（Java & Postgres）。另外，您的主机
OS也有一个tz数据副本。
