Parsing datetime from ISO 8601 using Spark SQL

Question

Want to do this but the other way around.

My dates are in this format YYYY-MM-DDThh:mm:ss, I want two columns YYYY-MM-DD and hh:mm that I can concat, if I want to, for certain queries.

I get an error when using convert(); I assume this is not supported currently with Spark SQL.

When I use date(datetime) or timestamp(datetime), I get all null values returned. However, minute(datetime) and hour(datetime) work.

Currently, using this

concat(date,' ', hour,':', (case when minute < 10 then concat('0',minute) else minute end)) as DateTime
from (select OtherDateOnlyColumn as date, minute(datetime) as minute, hour(datetime) as hour from ...)

which is obviously not efficient.

Answer 1

I just tried with date() on this query and it works:

select date(datetime) from df

Maybe the date in your table is string type; you should check the data types of the columns with

DESCRIBE your_table

If the date is string type, you can use cast(datetime as timestamp) as newTimestamp which is available in Spark SQL to convert the datetime back to a timestamp type and use variants of date_format(newTimestamp, 'YYYY-MM-dd hh:mm') from there.