datetime range filter in PySpark SQL

How about something like this:

import pyspark.sql.functions as func

df = df.select(func.to_date(df.my_col).alias("time"))
sf = df.filter(df.time > date_from).filter(df.time < date_to)

Lets assume your data frame looks as follows:

sf = sqlContext.createDataFrame([
    [datetime.datetime(2013, 6, 29, 11, 34, 29)],
    [datetime.datetime(2015, 7, 14, 11, 34, 27)],
    [datetime.datetime(2012, 3, 10, 19, 00, 11)],
    [datetime.datetime(2016, 2, 8, 12, 21)],
    [datetime.datetime(2014, 4, 4, 11, 28, 29)]
], ('my_col', ))

with schema:

root
 |-- my_col: timestamp (nullable = true)

and you want to find dates in a following range:

import datetime, time 
dates = ("2013-01-01 00:00:00",  "2015-07-01 00:00:00")

timestamps = (
    time.mktime(datetime.datetime.strptime(s, "%Y-%m-%d %H:%M:%S").timetuple())
    for s in dates)

It is possible to query using timestamps either computed on a driver side:

q1 = "CAST(my_col AS INT) BETWEEN {0} AND {1}".format(*timestamps)
sf.where(q1).show()

or using unix_timestamp function:

q2 = """CAST(my_col AS INT)
        BETWEEN unix_timestamp('{0}', 'yyyy-MM-dd HH:mm:ss')
        AND unix_timestamp('{1}', 'yyyy-MM-dd HH:mm:ss')""".format(*dates)

sf.where(q2).show()

It is also possible to use udf in a similar way I described in an another answer.

If you use raw SQL it is possible to extract different elements of timestamp using year, date, etc.

sqlContext.sql("""SELECT * FROM sf
    WHERE YEAR(my_col) BETWEEN 2014 AND 2015").show()

EDIT:

Since Spark 1.5 you can use built-in functions:

dates = ("2013-01-01",  "2015-07-01")
date_from, date_to = [to_date(lit(s)).cast(TimestampType()) for s in dates]

sf.where((sf.my_col > date_from) & (sf.my_col < date_to))

You can also use pyspark.sql.Column.between, which is inclusive of the bounds:

from pyspark.sql.functions import col
sf.where(col('my_col').between(*dates)).show(truncate=False)
#+---------------------+
#|my_col               |
#+---------------------+
#|2013-06-29 11:34:29.0|
#|2014-04-04 11:28:29.0|
#+---------------------+