别担心，要开心
postgres如何在时间戳上执行索引-换句话说，它将基于日期/时间为时间戳编制索引，还是以秒/毫秒为单位，等等？
Postgres使用的索引方案的内部结构通常应该对您透明，无论如何。请记住，您今天学习的实现可能会在Postgres的未来版本中发生变化。
您可能会陷入premature optimization的陷阱。信任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个八位字节）的整数。根据Wikipedia的说法，Postgres使用的纪元参考为2000-01-01，大概是该日期的第一时刻（UTC时间2000-01-01T00：00：00.0Z）。我们没有任何理由在乎使用什么纪元引用，但是您就可以了。
真正的要点是Postgres中的日期时间值仅存储为数字，计数为microseconds。时间戳类型不是您可能正在考虑的特定日期和时间。您的查询当然可以从timestamp列上的索引中受益，但是面向日期（无时间）的查询将不会特别受益。该索引不是面向日期的，也不是我将在下面解释的。
从某个时刻确定日期需要一个时区。在任何给定时刻，日期都会随时区在全球范围内变化。在巴黎午夜过后几分钟，法国是新的一天，而在魁北克蒙特利尔仍然是“昨天”。
要按日期查询时刻，您需要确定当天的第一时刻和第二天的第一时刻。然后，我们使用“半开式”方法来定义时间范围，其中开始是包含在内的，而结尾是排斥的。我们搜索等于或晚于开始时间但也早于结束时间的时刻。提示：“等于或晚于开始”的另一种说法是“不早于”。
您正在使用Java，因此可以在其中使用业界领先的 java.time 类。
java.time 类使用nanoseconds的分辨率，比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.
编写您的Half-Open SQL语句。 不要使用SQL命令BETWEEN，因为它不是半开的。
String sql = "SELECT * FROM tbl WHERE event !< ? && event < ? ;" ; // Half-Open query in SQL.
将您的开始和结束值传递给准备好的语句。
您的JDBC driver支持J DBC 4.2，并且以后可以通过使用PreparedStatement::setObject和ResultSet::getObject与大多数 java.time 一起使用。奇怪的是，JDBC规范不是要求支持两种最常用的类型：Instant（始终在UTC中）和ZonedDateTime。这些可能无法使您的特定驱动程序正常工作。该标准确实需要对OffsetDateTime的支持，因此让我们转换为该标准。
preparedStatement.setObject( 1,zdtStart.toOffsetDateTime() ) ;
preparedStatement.setObject( 2,zdtStop.toOffsetDateTime() ) ;
传递给OffsetDateTime的结果PreparedStatement对象将携带该日期时间在该时区使用的偏移量。对于调试或好奇心，您可能希望在UTC中查看这些值。因此，让我们通过提取Instant来适应UTC，然后应用零小时-分钟-秒的偏移量来获取OffsetDateTime带有UTC本身偏移量的东西。
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的列的值被物理存储，并且可以被索引。
注意
对于此类日期时间工作而言，至关重要的是有关当前时区定义的正确信息。通常，此信息来自tz data / IANA维护的ICANN。
Java和Postgres都包含自己的 tz数据副本。
世界各地的政客对重新定义时区表现出浓厚的兴趣，通常很少或根本没有警告。因此，请务必跟踪您关心的时区的变化。当您更新Java或Postgres时，您可能会获得tz数据的新副本。但是在某些情况下，您可能需要手动更新这两个环境中的一个或两个（Java和Postgres）。主机OS也有一个tz数据副本，fyi。
,
这是对 Percona recommends 的反刍。
他们推荐
BRIN 指数
.
我需要这个证明来获取按时间戳排序的记录集。即使示例使用时间戳，我也使用时间戳。
-
而且我的记录是按时间顺序排列的，旧的时间戳列不会更新或删除。
-
更新仅最近记录中的其他列。较旧的记录未受影响。
我的表将有几百万条记录。
您可以测试您的查询。我使用 pgAdmin。
CREATE TABLE testtab (id int NOT NULL PRIMARY KEY,date TIMESTAMP NOT NULL,level INTEGER,msg TEXT);
create index testtab_date_idx on testtab(date);
"Gather (cost=1000.00..133475.57 rows=1 width=49) (actual time=848.040..862.638 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on testtab (cost=0.00..132475.47 rows=1 width=49) (actual time=832.108..832.109 rows=0 loops=3)"
" Filter: ((date >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp without time zone))"
" Rows Removed by Filter: 2666667"
"Planning Time: 0.238 ms"
"Execution Time: 862.662 ms"
explain analyze select * from public.testtab where date between '2019-08-08 14:40:47.974791' and '2019-08-08 14:50:47.974791';
"Gather (cost=1000.00..133475.57 rows=1 width=49) (actual time=666.283..681.586 rows=0 loops=1)"
" Workers Planned: 2"
" Workers Launched: 2"
" -> Parallel Seq Scan on testtab (cost=0.00..132475.47 rows=1 width=49) (actual time=650.661..650.661 rows=0 loops=3)"
" Filter: ((date >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp without time zone))"
" Rows Removed by Filter: 2666667"
"Planning Time: 0.069 ms"
"Execution Time: 681.617 ms"
create index testtab_date_brin_idx on rm_owner.testtab using brin (date);
explain analyze select * from public.testtab where date between '2019-08-08 14:40:47.974791' and '2019-08-08 14:50:47.974791';
"Bitmap Heap Scan on testtab (cost=20.03..33406.84 rows=1 width=49) (actual time=0.143..0.143 rows=0 loops=1)"
" Recheck Cond: ((date >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp without time zone))"
" -> Bitmap Index Scan on "testtab_date_brin_idx " (cost=0.00..20.03 rows=12403 width=0) (actual time=0.141..0.141 rows=0 loops=1)"
" Index Cond: ((date >= '2019-08-08 14:40:47.974791'::timestamp without time zone) AND (date <= '2019-08-08 14:50:47.974791'::timestamp without time zone))"
"Planning Time: 0.126 ms"
"Execution Time: 0.161 ms"
更新：我看到的所有例子都与描述的一样here
