How to get today - "1 day" in sparksql?

Question

How to get current_date - 1 day in sparksql, same as cur_date()-1 in mysql.

Answer 1

Arithmetic functions allow you to perform arithmetic operations on columns containing dates.

For example, you can calculate the difference between two dates, add days to a date, or subtract days from a date. Built-in date arithmetic functions include datediff , date_add , date_sub , add_months , last_day , next_day and months_between .

From the foregoing, we need

date_sub (timestamp startdate, int days), target: subtracts the specified number of days from the TIMESTAMP value. The first argument can be a string that is automatically cast to TIMESTAMP if it uses a recognized format, as described in the TIMESTAMP data type. Return Type: timestamp

and we have

current_timestamp () Purpose: alias for the now () function. Return Type: timestamp

You can choose

 date_sub(CAST(current_timestamp() as DATE), 1) 

See https://spark.apache.org/docs/1.6.2/api/java/org/apache/spark/sql/functions.html.

Answer 2

You can easily complete this task, there are many methods related to date, and you can use date_sub here

Example for Spark-REPL:

  scala> spark.sql("select date_sub(current_timestamp(), 1)").show +----------------------------------------------+ |date_sub(CAST(current_timestamp() AS DATE), 1)| +----------------------------------------------+ | 2016-12-12| +----------------------------------------------+ 

Answer 3

You can try

 date_add(current_date(), -1) 

I don’t know the spark either, but found it on google. You can also use the link for reference.

Answer 4

Yes, date_sub() is suitable for the question, in any case there is an error in the selected answer:

Return Type: timestamp

Instead, the return type should be date , the date_sub () function truncates any part of the hh:mm:ss timestamp and returns only date .