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>I'm not familiar with postgresql, but you can convert the strings to the <code>struct_time</code> class which is part of the built in <a href="https://docs.python.org/3/library/time.html" rel="nofollow"><code>time</code> package</a> in Python and simply make comparisons between them.</p>

<pre><code>import time

time_data = ["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"]

def fn(t_init, t_fin, t_all):
    # Convert string inputs to struct_time using time.strptime()
    t_init, t_fin = [time.strptime(x, '%Y-%m-%d') for x in [t_init, t_fin]]
    t_all = [time.strptime(x, '%Y-%m-%dT%H:%M:%S+00:00') for x in time_all]
    out = []
    for jj in range(len(t_all)):
        if t_init &lt; t_all[jj] &lt; t_fin:
            out.append(jj)
    return out

out = fn('2016-05-12','2016-05-15', time_data)
print(out)
# [0, 1, 2]
</code></pre>

<p>The <code>time.strptime</code> routine uses a format specifiers to specify which parts of the string correspond to different time components.  </p>

<pre><code>%Y  Year with century as a decimal number.
%m  Month as a decimal number [01,12].
%d  Day of the month as a decimal number [01,31].
%H  Hour (24-hour clock) as a decimal number [00,23].
%M  Minute as a decimal number [00,59].
%S  Second as a decimal number [00,61].
%z  Time zone offset from UTC.
%a  Locale's abbreviated weekday name.
%A  Locale's full weekday name.
%b  Locale's abbreviated month name.
%B  Locale's full month name.
%c  Locale's appropriate date and time representation.
%I  Hour (12-hour clock) as a decimal number [01,12].
%p  Locale's equivalent of either AM or PM.
</code></pre>

python postgresql datetime pandas