Splitting Date into Year, Month and Day, with inco...

PranjalThapar · ‎05-04-2017

I am trying to split my Date Column which is a String Type right now into 3 columns Year, Month and Date. I use (PySpark):

<code>split_date=pyspark.sql.functions.split(df['Date'], '-')     
df= df.withColumn('Year', split_date.getItem(0))
df= df.withColumn('Month', split_date.getItem(1))
df= df.withColumn('Day', split_date.getItem(2))<br>

I run into an issue, because half my dates are separated by '-' and the other half are separated by '/'. How can I use and or operation to split the Date by either '-' or '/' depending on the use case. Additionaly, when its separated by '/', the format is mm/dd/yyyy and when separated by '-', the format is yyyy-mm-dd.

I want the Date column to be separated into Day, Month and Year.

Eve · ‎02-25-2019

Try this 🙂 It works for me on string type date columns, holding something like this inside: 2016-05-02T18:28:15.790+0000

df = df1.select("some_id", year(df1["date"]).alias('year'), month(df1["date"]).alias('month'), dayofmonth(df1["date"]).alias('day'), hour(df1["date"]).alias('hour')).show()

Eve · ‎02-25-2019

And in SCALA - assuming that df1 has a "date" column:

import org.apache.spark.sql.functions._ import org.apache.spark.sql.types._ import org.apache.spark.sql._

val df2 = df1.withColumn("year", year(col("date"))) .withColumn("month", month(col("date"))) .withColumn("day", dayofmonth(col("date"))) .withColumn("hour", hour(col("date")))

df2.show(Int.MaxValue)

youssefassouli · ‎02-26-2019

thank you so much that was halpful

Eve · ‎03-04-2019

Could you please mark it as an answer, if it was helpful? 🙂

Splitting Date into Year, Month and Day, with inconsistent delimiters