How to pick yesterday date in spark sql for a date in Unix Date Format?

Let's say you have a table called test, you can add another column called yesterday that holds yesterday's date as unix timestamp through:

spark.sql("select *,to_unix_timestamp(date_add(current_date(), -1),'yyyy-MM-dd') as yesterday from test")

Final output:

+------------+----------+
|        test| yesterday|
+------------+----------+
|        test|1662588000|
+------------+----------+

Good luck!