Spark DataFrame String type column to Timestamp/Date type column conversion

Question

I have dataframe with two string columns c1dt and c2tm and it's format is yyyymmdd and yyyymmddTHHmmss.SSSz respectively. Now I want to convert these columns into date type and timestamp type columns and I tried the following but it doesn't work it shows columns values as null.

val newdf = df.withColumn("c1dt", unix_timestmap("c1dt","yyyymmdd").cast("date").withColumn("c2tm","yyyymmddTHHmmss.SSSz").cast("timestamp"))

When I call newdf.show both columns values show as null. If I print original dataframe df I see date and timestamp values.

raam86 · Accepted Answer · 2017-10-11 13:09:02Z

-1

Since you timestamp format is not the default one your best bet is probably to create a udf.

def _stringToTs(s: String): Timestamp = {
  val format = new SimpleDateFormat("yyyymmddTHHmmss.SSSz")
  val date = format.parse(timestamp)
  new Timestamp(miliseconds);
}
import org.apache.spark.sql.functions.udf
val stringToTS = udf(_stringToTS)
val newdf = df.withColumn("c1dt", stringToTS($"c1dt").cast("date").withColumn("c2tm",stringToTS($"c2tm")))

In case you data is coming from a CSV you can specify the timestamp format before you load the data which will be faster overall

spark.read
      .format("csv")
      .option("inferSchema", "true") // Automatically infer data types
      .option("timestampFormat", "yyyymmddTHHmmss.SSSz")  
      .load("path")

edited Oct 11, 2017 at 13:09

answered Oct 11, 2017 at 11:53

raam86

6,8812 gold badges33 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Spark DataFrame String type column to Timestamp/Date type column conversion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related