Convert String to Timestamp in Spark/Hive

user_8957308 0
Viewed 119

I need to convert string to Timestamp.

The problem is that the input is coming from a csv file and contains date-time values such as:

Mar 3 2022 8:30AM

Apr 27 2022 7:37AM

If I use the following conversion:

to_timestamp(to_timestamp(trim(DateColumn), 'MMM dd yyyy h:mma'), 'yyyy-MM-dd HH:mm:ss')

It converts the date Apr 27 2022 7:37AM correctly, but throws error while converting Mar 3 2022 8:30AM because of the extra space between the Month and Date values and that the date 3 is not 03.

Is there a way to convert these 2 strings formats into Datetime?

hiveql apache-spark-sql apache-spark hive sql
1 Answers

It is recommended that you first uniformly replace multiple spaces with a single space, and then convert to timestamp.

val df1 = df.withColumn("ts", to_timestamp(regexp_replace(trim(col("ts")), "\\s+", " "), "MMM d y h:mma"))
user_170214290
Related