Querying postgresql for DateTime values between two dates
Question: I have the following dateTime text type variable in Postgres table

    
    
    "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"
    

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

    
    
    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
    

I need to create dStart and dEnd variables and use them as function parameters
as below

    
    
       fn('2016-05-12','2016-05-15')
    

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


Answer: I'm not familiar with postgresql, but you can convert the strings to the
`struct_time` class which is part of the built in [`time`
package](https://docs.python.org/3/library/time.html) in Python and simply
make comparisons between them.

    
    
    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 < t_all[jj] < t_fin:
                out.append(jj)
        return out
    
    out = fn('2016-05-12','2016-05-15', time_data)
    print(out)
    # [0, 1, 2]
    

The `time.strptime` routine uses a format specifiers to specify which parts of
the string correspond to different time components.

    
    
    %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.
    

