title: Using BETWEEN operator with timestamp values in Postgres
body: <p>Recently I've been informed by a StackOverflow user that using <code>BETWEEN</code> operator with values of data type <code>timestamp without time zone</code> <strong>should not</strong> be used.  Below is the quote.</p>
  
  <blockquote>
    <p>Between means >= and &lt;= and shall not be used with ranges that contain timestamps.</p>
  </blockquote>
  
  <p>When asked for an explanation of this thesis or a link to Postgres documentation where it states that I've got an answer saying</p>
  
  <blockquote>
    <p>Why would such a simple thing need a site with documentation. I am sure you can find many anyway if you google (at least my detailed posts on various forums demonstrating the case)</p>
  </blockquote>
  
  <p>Well I googled. And found nothing that would advise against using this operator with timestamp values. In fact <a href="https://stackoverflow.com/a/8860632/2095534">this answer</a> on SO uses them and so does <a href="https://www.postgresql.org/message-id/20070813143728.GA91805%40winnie.fuhr.org" rel="nofollow noreferrer">this mailing group post</a>.</p>
  
  <p>I was informed that all these years I was doing it wrong. Is it really the case?</p>
  
  <p>As far as I know Postgres max precision for a timestamp is <code>1 microsecond</code> - correct me if I'm wrong. Thus aren't below statements equivalent ?</p>
  
  <pre><code>sample_date BETWEEN x AND y::timestamp - INTERVAL '1 microsecond'
  </code></pre>
  
  <p>and</p>
  
  <pre><code>sample_date &gt;= x AND sample_date &lt; y
  </code></pre>
  
  <p><em>Edit</em>: The sample is just a consideration of the difference. I'm aware of the fact that developers can miss the time part, but assuming one knows how it behaves, why should it <strong>not be used</strong>? Generally speaking, this is merely a sample, but I'm wondering about the bigger scope. I've been investigating the planner and it seems to be parsing <code>BETWEEN</code> to <code>&gt;= AND &lt;=</code>.</p>
  
  <p>Why does one preferably write <code>&gt;= AND &lt;=</code> than <code>BETWEEN</code> in the matter of <strong>results</strong> - not including the time to translate it?</p>