topic Splitting Date into Year, Month and Day, with inconsistent delimiters in Data Engineering

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

PranjalThapar — Thu, 04 May 2017 19:52:26 GMT

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.

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

Eve — Mon, 25 Feb 2019 14:20:14 GMT

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()

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

Eve — Tue, 26 Feb 2019 07:16:40 GMT

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)

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

youssefassouli — Tue, 26 Feb 2019 11:05:19 GMT

thank you so much that was halpful

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

Eve — Tue, 05 Mar 2019 06:53:15 GMT

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