Casting date to integer returns null in Spark SQL

Question

I want to convert a date column into integer using Spark SQL. I'm following this code, but I want to use Spark SQL and not PySpark.

Reproduce the example:

from pyspark.sql.types import *
import pyspark.sql.functions as F

# DUMMY DATA
simpleData = [("James",34,"2006-01-01","true","M",3000.60),
              ("Michael",33,"1980-01-10","true","F",3300.80),
              ("Robert",37,"1992-07-01","false","M",5000.50)
             ]

columns = ["firstname","age","jobStartDate","isGraduated","gender","salary"]
df = spark.createDataFrame(data = simpleData, schema = columns)

df = df.withColumn("jobStartDate", df['jobStartDate'].cast(DateType()))
df = df.withColumn("jobStartDateAsInteger1", F.unix_timestamp(df['jobStartDate']))
display(df)

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?

ZygD · Accepted Answer · 2022-05-24 20:22:24Z

1

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

answered May 24, 2022 at 20:22

ZygD

24.8k41 gold badges107 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Enrique Benito Casado Over a year ago

I know they recommend the commen to improve the answer and not to say thanks but Thanks Thanks thanks !!!!!!! really Thanks !! I had lost hope that it could be done

Collectives™ on Stack Overflow

Casting date to integer returns null in Spark SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related