title: Sqlite datetime comparison with a unix timestamp
body: <p>The Sqlite documentation <a href="https://sqlite.org/quirks.html" rel="nofollow noreferrer">states</a>:</p>
  <blockquote>
  <p>SQLite has no DATETIME datatype. Instead, dates and times can be stored in any of these ways:</p>
  <ul>
  <li>As a TEXT string in the ISO-8601 format. Example: '2018-04-02 12:13:46'.</li>
  <li>As an INTEGER number of seconds since 1970 (also known as &quot;unix time&quot;).<br />
  ...</li>
  </ul>
  </blockquote>
  <p>so I decided to use an <code>INTEGER</code> unix timestamp:</p>
  <pre><code>import sqlite3, time
  conn = sqlite3.connect(':memory:')
  conn.execute(&quot;CREATE TABLE data(datetime INTEGER, t TEXT);&quot;)
  conn.execute(&quot;INSERT INTO data VALUES (CURRENT_TIMESTAMP, 'hello')&quot;)
  </code></pre>
  <p>Why does the following query return no result?</p>
  <pre><code>ts = int(time.time()) + 31*24*3600  # unix timestamp 1 month in the future
  print(list(conn.execute(&quot;SELECT * FROM data WHERE datetime &lt;= ?&quot;, (ts, ))))
  </code></pre>
  <p><strong>More generally, how to do a <code>SELECT</code> query with a comparison with a unix timestamp with Sqlite?</strong></p>
  <hr />
  <p>PS:</p>
  <ul>
  <li><p>I have already read <a href="https://stackoverflow.com/questions/1975737/sqlite-datetime-comparison">SQLite DateTime comparison</a> and similar questions, which offer other comparison methods, but here I'd like to precisely discuss why this unix timestamp comparison does not work.</p>
  </li>
  <li><p>For performance reasons, I'd like to:</p>
  <ul>
  <li>do a query that compares <em>integers</em> (which is super fast if many rows): <code>WHERE datetime &lt;= unix_timestamp</code>,</li>
  <li>avoid to convert <code>unix_timestamp</code> into string, and then compare <code>datetime</code> to this string (I guess it'll be far slower)</li>
  </ul>
  </li>
  </ul>