Incorrect result potentailly caused by date format conversion? Can anyone help to fix? Thanks lots


#1

I have a DF as below:

scala> val patDF = patRDD.toDF(“patientlD”,“name”,“dateOtBirth”,“lastVisitDate”)
scala> patDF.show
±--------±------±----------±------------+
|patientlD| name|dateOtBirth|lastVisitDate|
±--------±------±----------±------------+
| 1001|Ah Teck| 1991-12-31| 2012-01-20|
| 1002| Kumar| 2011-10-29| 2012-09-20|
| 1003| Ali| 2011-01-30| 2012-10-21|
±--------±------±----------±------------+

The DF is registered as a temp table:
patDF.registerTempTable(“patients”)

Now I want to get the records between 2012-09-15 and now:

SELECT * FROM patients WHERE from_unixtime(unix_timestamp(lastVisitDate, ‘yyyy-mm-dd’)) between ‘2012-09-15’ and current_timestamp() order by lastVisitDate

i.e.
val results2 = sqlContext.sql(“SELECT * FROM patients WHERE from_unixtime(unix_timestamp(lastVisitDate, ‘yyyy-mm-dd’)) between ‘2012-09-15’ and current_timestamp() order by lastVisitDate”)

Presumably there should be 2 records: 1002 and 1003

I got nothing though.


Anything wrong with the sql string?

Thank you very much.


#2

May i know what was passed for the RDD.
thank you


#3

Thank you, the root cause is the format string, which should be yyyy-MM-dd, not yyyy-mm-dd