I have a data frame in spark 1.6 that I would like to select all rows greater than the current time. I am filtering on "time_occurred" column with this type of format "yyyy-MM-dd'T'HH:mm:ss.SSS". I was wondering what the best way is to achieve this?
1 Answers
Best way would be casting the field to timestamp type by using Regexp_replace function to replace 'T'.
Then by using current_timestamp function we can filter out data in the dataframe.
Example:
Spark-scala-1.6:
import sqlContext.implicits._
import org.apache.spark.sql.functions._
import org.apache.spark.sql.types._
//sample data
val df=sc.parallelize(Seq(("2019-10-17'T'18:30:45.123"),("2019-10-15'T'18:30:45.123"))).toDF("ts")
df.filter(regexp_replace('ts,"'T'"," ").cast("timestamp") > current_timestamp).show(false)
Result:
+-------------------------+
|ts |
+-------------------------+
|2019-10-17'T'18:30:45.123|
+-------------------------+
In case if you need to replace 'T' to get timestamp type for ts field then use this approach.
df.withColumn("ts",regexp_replace('ts,"'T'"," ").cast("timestamp"))
.filter('ts > current_timestamp).show(false)
Result:
+-----------------------+
|ts |
+-----------------------+
|2019-10-17 18:30:45.123|
+-----------------------+
Result ts field will be having Timestamp type.
Related