Querying postgresql for DateTime values between two dates
<p>I have the following dateTime text type variable in Postgres table</p>

<pre><code>"2016-05-12T23:59:11+00:00"          
"2016-05-13T11:00:11+00:00"
"2016-05-13T23:59:11+00:00"
"2016-05-15T10:10:11+00:00"
"2016-05-16T10:10:11+00:00"
"2016-05-17T10:10:11+00:00"
</code></pre>

<p>I have to write a Python function to extract the data for a few variables between two dates</p>

<pre><code>def fn(dateTime):
    df1=pd.DataFrame()
    query = """ SELECT "recordId" from "Table" where "dateTime" BETWEEN %s AND %s """ %(dStart,dEnd) 
    df1=pd.read_sql_query(query1,con=engine)
    return df1
</code></pre>

<p>I need to create dStart and dEnd variables and use them as function parameters as below</p>

<pre><code>   fn('2016-05-12','2016-05-15')
</code></pre>

<p>I tried using to_char("dateTime", 'YYYY-MM-DD') Postgres function but didn't work out. Please let me know how to solve this</p>

<p>When working with sql, you should always use your sql library to substitute parameters into the query, instead of using Python's string operators. This avoids the risk of malformed queries or sql injection attacks. See e.g., <a href="http://bugcharmer.blogspot.com/2013/03/basics-avoiding-sql-injection.html" rel="nofollow">this page</a>. Right now your code won't run because it directly inserts <code>dStart</code> and <code>dEnd</code> without any quoting, so they are interpreted as mathematical expressions (2016 - 5 - 12 = 1999).</p>

<p>There's also a secondary problem that your query will exclude <code>dateTime</code> values <em>on</em> the end date, because <code>endDate</code> will be treated as having a time value of 00:00:00 when it is compared toÂ <code>dateTime</code>. And if you use <code>to_char()</code> or some other function to extract just the date from the <code>dateTime</code> column to do the comparison, it will prevent your query from using indexes, making it very inefficient. </p>

<p>Here is some revised code that may work for you:</p>

<pre><code>def fn(dStart, dEnd):
    query = """ 
        SELECT "recordId" 
        FROM "Table" 
        WHERE "dateTime" &gt;= %(start)s AND "dateTime" &lt; %(end)s + interval '1 day'
    """
    query_params = {'start': dStart, 'end': dEnd}
    df1 = pd.read_sql_query(query1, con=engine, params=query_params)
    return df1
</code></pre>

<p>This code relies on a few assumptions (welcome to the wonderful world of datetime querying!): </p>

<ol>
<li>you will pass <code>dStart</code> and <code>dEnd</code> to <code>fn()</code>, instead of just a single <code>dateTime</code>, </li>
<li>the <code>dateTime</code> column is type <code>timestamp with timezone</code> (not <code>text</code>), </li>
<li>the timezones in the <code>dateTime</code> column are correct, and</li>
<li>the dates given by <code>dStart</code> and <code>dEnd</code> are in the server's timezone or you have used <code>SET TIMEZONE ...</code> with your <code>engine</code> object to select the right time zone to use for this session.</li>
</ol>

<h3>Notes</h3>

<p>Different database engines use <a href="https://www.python.org/dev/peps/pep-0249/#paramstyle" rel="nofollow">different placeholders</a> for the parameters, so you will need to <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.read_sql_query.html" rel="nofollow">check your database driver's documentation</a> to decide what placeholders to use. The code above should work fine for postgresql.</p>

<p>With the code above, <code>dStart</code> and <code>dEnd</code> will be inserted into the query as strings, and postgresql automatically convert them into timestamps when it runs the query. This should work fine for the example dates you gave, but if you need more direct control, you have two options:</p>

<ol>
<li>call <code>fn()</code> with Python <code>date</code> or <code>datetime</code> values for <code>dStart</code> and <code>dEnd</code>, and the code above will insert them into the query as postgresql dates or timestamps; or </li>
<li>explicitly convert the <code>dStart</code> and <code>dEnd</code> strings into postgresql dates by replacing <code>%(start)s</code> and <code>%(end)s</code> with something like this: <code>to_date(%(start)s, 'YYYY-MM-DD')</code>.</li>
</ol>

python postgresql datetime pandas