blocks|key|80296|text|tl;dr|type|unstyled|depth|inlineStyleRanges|entityRanges|data|80297|使用半开放方法，>=+AND+<逻辑，对于时间跨度，而不是BETWEEN。|offset|length|style|CODE|80298|错误的逻辑|80299|80300|开始时间-->+10月14日10+pm结束时间-->+10月14日11+pm…然后，我必须处理开始时间大于传递的值、结束时间小于传递的值的所有行|blockquote|80301|80302|80303|这个逻辑是不正确的。用这样的逻辑做这样的每小时报告，你将永远不会报告当时钟敲响一个小时时碰巧记录的项目。不会找到值正好在10+AM+(10:00:00.0)或11+AM+(11:00:00.0)的行。|80304|半开|80305|我建议一致地使用半开放方法来定义时间跨度。在这种方法中，开始是包容性的，而结尾是排他性的。例如，该小时从上午10点的第一个时刻开始，一直持续到(但不包括)时钟敲击上午11点|80306|使用这种方法，我将提供一系列每小时一次的查询，查找所有行。任何行都不会像您的逻辑那样消失。|80307|SELECT+*+
FROM+event_
WHERE+when_+>=+?
AND+when_+<+?
;|code-block|syntax|javascript|80308|换句话说，从不将+BETWEEN与时间跨度一起使用。请改用>=+AND+<逻辑。|BOLD|80309|时区|80310|如果要讨论实际时刻，如果要讨论TIMESTAMP+WITH+TIME+ZONE的SQL标准列类型，则时区问题至关重要。|80311|在你的例子中，你是指法国巴黎的上午10点吗？或者上午10点在魁北克省蒙特勒阿勒市，几个小时后？或者印度加尔各答上午10点，几个小时前？|80312|在Java语言中，通过ZonedDateTime类将您的条件定义为使用时区的时刻。|80313|ZoneId+z+=+ZoneId.of(+"Pacific/Auckland"+)+;
LocalDate+ld+=+LocalDate.of(+2018+,+10+,+14+)+;
LocalTime+lt+=+LocalTime.of(+10+,+0+)+;
ZonedDateTime+zdt+=+ZonedDateTime.of(+ld+,+lt+,+z+)+;|80314|要查看以协调世界时为单位的相同时刻，请提取Instant。|80315|Instant+instant+=+zdt.toInstant()+;|80316|生成以标准ISO+8601格式表示其值的文本。|80317|String+output+=+instant.toString()+;+++//+Generate+text+in+standard+ISO+8601+format.+|80318|使用与JDBC+4.2或更高版本兼容的JDBC驱动程序，在准备好的语句中将其传递给SQL查询。|80319|myPreparedStatement.setObject(+1+,+zdtStart+)+;
myPreparedStatement.setObject(+2+,+zdtStop+)+;|80320|80321|关于java.time|80322|框架内置于Java8和更高版本中。这些类取代了麻烦的旧legacy日期时间类，如java.util.Date、Calendar和SimpleDateFormat。|80323|现在在maintenance+mode中的项目建议迁移到java.time类。|80324|要了解更多信息，请参阅。和搜索堆栈溢出，以获得许多示例和解释。规范为JSR+310。|80325|您可以直接与数据库交换java.time对象。使用与JDBC+4.2或更高版本兼容的JDBC+driver。不需要字符串，也不需要java.sql.*类。|80326|从哪里获取java.time类？|80327|80328|，、、和更高版本-标准Java+API的一部分，具有捆绑的实现。|unordered-list-item|80329|Java9添加了一些次要功能和fixes.|80330|80331|80332|​|80333|80334|和.|80335|中的+|80336|大多数java.time功能已向后移植到Java6和7|80337|80338|80339|80340|+|80341|更高版本的Android捆绑实现的java.time类。|80342|对于较早的Android+(<26)，项目适配+(如上所述)。请参阅.|80343|80344|80345|80346|项目使用额外的类扩展了java.time。这个项目是未来可能添加到java.time中的试验场。您可能会在这里找到一些有用的类，如Interval、YearWeek、YearQuarter和more。|80347|entityMap|0|LINK|mutability|MUTABLE|url|https://en.wikipedia.org/wiki/Legacy_system|1|https://docs.oracle.com/javase/10/docs/api/java/util/Date.html|2|https://docs.oracle.com/javase/10/docs/api/java/util/Calendar.html|3|http://docs.oracle.com/javase/10/docs/api/java/text/SimpleDateFormat.html|4|https://en.wikipedia.org/wiki/Maintenance_mode|5|http://docs.oracle.com/javase/10/docs/api/java/time/package-summary.html|6|https://jcp.org/en/jsr/detail?id=310|7|http://openjdk.java.net/jeps/170|8|https://en.wikipedia.org/wiki/JDBC_driver|9|http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/Interval.html|10|http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/YearWeek.html|11|http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/YearQuarter.html|12|http://www.threeten.org/threeten-extra/apidocs/index.html^0|0|8|8|T|7|0|0|0|0|0|0|0|0|0|0|0|5|3|9|7|9|7|T|8|0|0|F|O|0|0|B|D|0|0|L|7|0|0|0|0|0|0|0|0|14|E|1J|8|1S|G|R|6|0|14|E|1|1J|8|2|1S|G|3|0|3|G|4|S|9|5|0|Y|7|6|0|1T|A|Q|8|7|16|B|8|0|0|0|1|1|0|0|0|0|0|0|0|0|0|0|1|1|1|0|0|0|1T|8|22|8|2B|B|1T|8|9|22|8|A|2B|B|B|2N|4|C|0^^$0|@$1|2|3|4|5|6|7|3V|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|3W|8|@$D|3X|E|3Y|F|G]|$D|3Z|E|40|F|G]]|9|@]|A|$]]|$1|H|3|I|5|6|7|41|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|42|8|@]|9|@]|A|$]]|$1|K|3|L|5|M|7|43|8|@]|9|@]|A|$]]|$1|N|3|-4|5|6|7|44|8|@]|9|@]|A|$]]|$1|O|3|-4|5|6|7|45|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|46|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|47|8|@]|9|@]|A|$]]|$1|T|3|U|5|6|7|48|8|@]|9|@]|A|$]]|$1|V|3|W|5|6|7|49|8|@]|9|@]|A|$]]|$1|X|3|Y|5|Z|7|4A|8|@]|9|@]|A|$10|11]]|$1|12|3|13|5|6|7|4B|8|@$D|4C|E|4D|F|14]|$D|4E|E|4F|F|14]|$D|4G|E|4H|F|G]|$D|4I|E|4J|F|G]]|9|@]|A|$]]|$1|15|3|16|5|6|7|4K|8|@]|9|@]|A|$]]|$1|17|3|18|5|6|7|4L|8|@$D|4M|E|4N|F|G]]|9|@]|A|$]]|$1|19|3|1A|5|6|7|4O|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|6|7|4P|8|@$D|4Q|E|4R|F|G]]|9|@]|A|$]]|$1|1D|3|1E|5|Z|7|4S|8|@]|9|@]|A|$10|11]]|$1|1F|3|1G|5|6|7|4T|8|@$D|4U|E|4V|F|G]]|9|@]|A|$]]|$1|1H|3|1I|5|Z|7|4W|8|@]|9|@]|A|$10|11]]|$1|1J|3|1K|5|6|7|4X|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|Z|7|4Y|8|@]|9|@]|A|$10|11]]|$1|1N|3|1O|5|6|7|4Z|8|@]|9|@]|A|$]]|$1|1P|3|1Q|5|Z|7|50|8|@]|9|@]|A|$10|11]]|$1|1R|3|-4|5|6|7|51|8|@]|9|@]|A|$]]|$1|1S|3|1T|5|6|7|52|8|@]|9|@]|A|$]]|$1|1U|3|1V|5|6|7|53|8|@$D|54|E|55|F|G]|$D|56|E|57|F|G]|$D|58|E|59|F|G]]|9|@$D|5A|E|5B|1|5C]|$D|5D|E|5E|1|5F]|$D|5G|E|5H|1|5I]|$D|5J|E|5K|1|5L]]|A|$]]|$1|1W|3|1X|5|6|7|5M|8|@]|9|@$D|5N|E|5O|1|5P]|$D|5Q|E|5R|1|5S]]|A|$]]|$1|1Y|3|1Z|5|6|7|5T|8|@]|9|@$D|5U|E|5V|1|5W]]|A|$]]|$1|20|3|21|5|6|7|5X|8|@$D|5Y|E|5Z|F|G]]|9|@$D|60|E|61|1|62]|$D|63|E|64|1|65]]|A|$]]|$1|22|3|23|5|6|7|66|8|@]|9|@]|A|$]]|$1|24|3|-4|5|6|7|67|8|@]|9|@]|A|$]]|$1|25|3|26|5|27|7|68|8|@]|9|@]|A|$]]|$1|28|3|29|5|27|7|69|8|@]|9|@]|A|$]]|$1|2A|3|-4|5|6|7|6A|8|@]|9|@]|A|$]]|$1|2B|3|-4|5|6|7|6B|8|@]|9|@]|A|$]]|$1|2C|3|2D|5|6|7|6C|8|@]|9|@]|A|$]]|$1|2E|3|-4|5|6|7|6D|8|@]|9|@]|A|$]]|$1|2F|3|2G|5|27|7|6E|8|@]|9|@]|A|$]]|$1|2H|3|2I|5|6|7|6F|8|@]|9|@]|A|$]]|$1|2J|3|2K|5|27|7|6G|8|@]|9|@]|A|$]]|$1|2L|3|-4|5|6|7|6H|8|@]|9|@]|A|$]]|$1|2M|3|2D|5|6|7|6I|8|@]|9|@]|A|$]]|$1|2N|3|-4|5|6|7|6J|8|@]|9|@]|A|$]]|$1|2O|3|2P|5|27|7|6K|8|@]|9|@]|A|$]]|$1|2Q|3|2R|5|27|7|6L|8|@]|9|@]|A|$]]|$1|2S|3|2T|5|27|7|6M|8|@]|9|@]|A|$]]|$1|2U|3|-4|5|6|7|6N|8|@]|9|@]|A|$]]|$1|2V|3|-4|5|6|7|6O|8|@]|9|@]|A|$]]|$1|2W|3|2D|5|6|7|6P|8|@]|9|@]|A|$]]|$1|2X|3|2Y|5|6|7|6Q|8|@$D|6R|E|6S|F|G]|$D|6T|E|6U|F|G]|$D|6V|E|6W|F|G]]|9|@$D|6X|E|6Y|1|6Z]|$D|70|E|71|1|72]|$D|73|E|74|1|75]|$D|76|E|77|1|78]]|A|$]]|$1|2Z|3|-4|5|6|7|79|8|@]|9|@]|A|$]]]|30|$31|$5|32|33|34|A|$35|36]]|37|$5|32|33|34|A|$35|38]]|39|$5|32|33|34|A|$35|3A]]|3B|$5|32|33|34|A|$35|3C]]|3D|$5|32|33|34|A|$35|3E]]|3F|$5|32|33|34|A|$35|3G]]|3H|$5|32|33|34|A|$35|3I]]|3J|$5|32|33|34|A|$35|3K]]|3L|$5|32|33|34|A|$35|3M]]|3N|$5|32|33|34|A|$35|3O]]|3P|$5|32|33|34|A|$35|3Q]]|3R|$5|32|33|34|A|$35|3S]]|3T|$5|32|33|34|A|$35|3U]]]]

<h1>tl;dr</h1>

Use Half-Open approach, <code>&gt;= AND &lt;</code> logic, for a span-of-time, never <code>BETWEEN</code>. 

<h1>Incorrect logic</h1>

<blockquote>
 start time --> oct 14 10 pm end time --> oct 14 11 pm … then i must all rows whose start time greater than my passed value and end time less than my passed value
</blockquote>

This logic is incorrect. Doing such hourly reports with such logic, You will never report on the items that happen to be logged when the clock strikes an hour. Rows with a value right at 10 AM exactly (10:00:00.0) or 11 AM exactly (11:00:00.0) will never be found. 

<h1>Half-Open</h1>

I suggest making consistent use of the Half-Open approach to define span-of-time. In this approach, the beginning is inclusive while the ending is exclusive. For example, the hour begins with the first moment of the 10 AM hour, and runs up to, but does not include, when the clock strikes 11 AM. 

With this approach a series of hourly queries will I feed find all rows. No rows will be disappearing as they would with your logic. 

<pre><code>SELECT * 
FROM event_
WHERE when_ &gt;= ?
AND when_ &lt; ?
;
</code></pre>

In other words, never use <code>BETWEEN</code> with a span-of-time. Use <code>&gt;= AND &lt;</code> logic instead. 

<h1>Time zone</h1>

The question of time zone is crucial if talking about actual moments, if talking about the SQL-standard column type of <code>TIMESTAMP WITH TIME ZONE</code>.

In your example, do you mean 10 AM in Paris France? Or 10 AM in Montréal Québec, some hours later? Or 10 AM in Kolkata India, some hours earlier?

In Java, define your criteria as moments using a time zone with the <code>ZonedDateTime</code> class. 

<pre><code>ZoneId z = ZoneId.of( "Pacific/Auckland" ) ;
LocalDate ld = LocalDate.of( 2018 , 10 , 14 ) ;
LocalTime lt = LocalTime.of( 10 , 0 ) ;
ZonedDateTime zdt = ZonedDateTime.of( ld , lt , z ) ;
</code></pre>

To view that same moment in UTC, extract an <code>Instant</code>. 

<pre><code>Instant instant = zdt.toInstant() ;
</code></pre>

Generate text representing its value in standard ISO 8601 format. 

<pre><code>String output = instant.toString() ; // Generate text in standard ISO 8601 format. 
</code></pre>

Pass that to the SQL query in a prepared statement using a JDBC driver compliant with JDBC 4.2 or later. 

<pre><code>myPreparedStatement.setObject( 1 , zdtStart ) ;
myPreparedStatement.setObject( 2 , zdtStop ) ;
</code></pre>

<hr>

<h1>About java.time</h1>

The <a href="http://docs.oracle.com/javase/10/docs/api/java/time/package-summary.html" rel="nofollow noreferrer">java.time</a> framework is built into Java 8 and later. These classes supplant the troublesome old <a href="https://en.wikipedia.org/wiki/Legacy_system" rel="nofollow noreferrer">legacy</a> date-time classes such as <a href="https://docs.oracle.com/javase/10/docs/api/java/util/Date.html" rel="nofollow noreferrer"><code>java.util.Date</code></a>, <a href="https://docs.oracle.com/javase/10/docs/api/java/util/Calendar.html" rel="nofollow noreferrer"><code>Calendar</code></a>, &amp; <a href="http://docs.oracle.com/javase/10/docs/api/java/text/SimpleDateFormat.html" rel="nofollow noreferrer"><code>SimpleDateFormat</code></a>.

The <a href="http://www.joda.org/joda-time/" rel="nofollow noreferrer">Joda-Time</a> project, now in <a href="https://en.wikipedia.org/wiki/Maintenance_mode" rel="nofollow noreferrer">maintenance mode</a>, advises migration to the <a href="http://docs.oracle.com/javase/10/docs/api/java/time/package-summary.html" rel="nofollow noreferrer">java.time</a> classes.

To learn more, see the <a href="http://docs.oracle.com/javase/tutorial/datetime/TOC.html" rel="nofollow noreferrer">Oracle Tutorial</a>. And search Stack Overflow for many examples and explanations. Specification is <a href="https://jcp.org/en/jsr/detail?id=310" rel="nofollow noreferrer">JSR 310</a>.

You may exchange java.time objects directly with your database. Use a <a href="https://en.wikipedia.org/wiki/JDBC_driver" rel="nofollow noreferrer">JDBC driver</a> compliant with <a href="http://openjdk.java.net/jeps/170" rel="nofollow noreferrer">JDBC 4.2</a> or later. No need for strings, no need for <code>java.sql.*</code> classes.

Where to obtain the java.time classes? 

<ul>
<li><a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_8" rel="nofollow noreferrer">Java SE 8</a>, <a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_9" rel="nofollow noreferrer">Java SE 9</a>, <a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_10" rel="nofollow noreferrer">Java SE 10</a>, <a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_11" rel="nofollow noreferrer">Java SE 11</a>, and later - Part of the standard Java API with a bundled implementation.

<ul>
<li>Java 9 adds some minor features and fixes.</li>
</ul></li>
<li><a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_6" rel="nofollow noreferrer">Java SE 6</a> and <a href="https://en.wikipedia.org/wiki/Java_version_history#Java_SE_7" rel="nofollow noreferrer">Java SE 7</a>

<ul>
<li>Most of the java.time functionality is back-ported to Java 6 &amp; 7 in <a href="http://www.threeten.org/threetenbp/" rel="nofollow noreferrer">ThreeTen-Backport</a>.</li>
</ul></li>
<li><a href="https://en.wikipedia.org/wiki/Android_(operating_system)" rel="nofollow noreferrer">Android</a>

<ul>
<li>Later versions of Android bundle implementations of the java.time classes.</li>
<li>For earlier Android (&lt;26), the <a href="https://github.com/JakeWharton/ThreeTenABP" rel="nofollow noreferrer">ThreeTenABP</a> project adapts <a href="http://www.threeten.org/threetenbp/" rel="nofollow noreferrer">ThreeTen-Backport</a> (mentioned above). See <a href="http://stackoverflow.com/q/38922754/642706">How to use ThreeTenABP…</a>.</li>
</ul></li>
</ul>

The <a href="http://www.threeten.org/threeten-extra/" rel="nofollow noreferrer">ThreeTen-Extra</a> project extends java.time with additional classes. This project is a proving ground for possible future additions to java.time. You may find some useful classes here such as <a href="http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/Interval.html" rel="nofollow noreferrer"><code>Interval</code></a>, <a href="http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/YearWeek.html" rel="nofollow noreferrer"><code>YearWeek</code></a>, <a href="http://www.threeten.org/threeten-extra/apidocs/org/threeten/extra/YearQuarter.html" rel="nofollow noreferrer"><code>YearQuarter</code></a>, and <a href="http://www.threeten.org/threeten-extra/apidocs/index.html" rel="nofollow noreferrer">more</a>.

I have 2 timestamp fileds in postgres and I am representing this 2 fields in java as Date fields.
start time --> oct 14 10 pm
end time --> oct 14 11 pm
if I pass a value as "oct 14 10.30 pm" then i must all rows whose start time greater than my passed value and end time less than my passed value

How to get all data between 2 timestamp in postgres.

数据库

Android

Java

开始时间--> 10月14 10 pm结束时间--> 10月14 11 pm如果我传递一个值为"oct 14 10:30 pm“，则我必须将其开始时间大于传递的值、结束时间小于传递的值的所有行

问如何在postgres中获取两个时间戳之间的所有数据。
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在postgres中获取两个时间戳之间的所有数据。EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在postgres中获取两个时间戳之间的所有数据。
EN