Casting date to integer returns null in Spark SQL-CodePudding

I want to convert a date column into integer using Spark SQL. I'm following

What I want is to do the same transformation, but using Spark SQL. I am using the following code:

df.createOrReplaceTempView("date_to_integer")

%sql
select
seg.*,
CAST (jobStartDate AS INTEGER) as JobStartDateAsInteger2 -- return null value
from date_to_integer seg

How to solve it?

CodePudding user response：

First you need to CAST your jobStartDate to DATE and then use UNIX_TIMESTAMP to transform it to UNIX integer.

SELECT
    seg.*,
    UNIX_TIMESTAMP(CAST (jobStartDate AS DATE)) AS JobStartDateAsInteger2
FROM date_to_integer seg