pyspark.sql.functions.try_subtract#

pyspark.sql.functions.try_subtract(left, right)[source]#

Returns left-right and the result is null on overflow. The acceptable input types are the same with the - operator.

New in version 3.5.0.

Parameters

leftColumn or column name

rightColumn or column name

Examples

Example 1: Integer minus Integer.

>>> import pyspark.sql.functions as sf
>>> spark.createDataFrame(
...     [(1982, 15), (1990, 2)], ["birth", "age"]
... ).select("*", sf.try_subtract("birth", "age")).show()
+-----+---+------------------------+
|birth|age|try_subtract(birth, age)|
+-----+---+------------------------+
| 1982| 15|                    1967|
| 1990|  2|                    1988|
+-----+---+------------------------+

Example 2: Date minus Integer.

>>> import pyspark.sql.functions as sf
>>> spark.sql(
...     "SELECT * FROM VALUES (DATE('2015-10-01')) AS TAB(date)"
... ).select("*", sf.try_subtract("date", sf.lit(1))).show()
+----------+---------------------+
|      date|try_subtract(date, 1)|
+----------+---------------------+
|2015-10-01|           2015-09-30|
+----------+---------------------+

Example 3: Date minus Interval.

>>> import pyspark.sql.functions as sf
>>> spark.sql(
...     "SELECT * FROM VALUES (DATE('2015-09-30'), INTERVAL 1 YEAR) AS TAB(date, itvl)"
... ).select("*", sf.try_subtract("date", "itvl")).show()
+----------+-----------------+------------------------+
|      date|             itvl|try_subtract(date, itvl)|
+----------+-----------------+------------------------+
|2015-09-30|INTERVAL '1' YEAR|              2014-09-30|
+----------+-----------------+------------------------+

Example 4: Interval minus Interval.

>>> import pyspark.sql.functions as sf
>>> spark.sql(
...     "SELECT * FROM VALUES (INTERVAL 1 YEAR, INTERVAL 2 YEAR) AS TAB(itvl1, itvl2)"
... ).select("*", sf.try_subtract("itvl1", "itvl2")).show()
+-----------------+-----------------+--------------------------+
|            itvl1|            itvl2|try_subtract(itvl1, itvl2)|
+-----------------+-----------------+--------------------------+
|INTERVAL '1' YEAR|INTERVAL '2' YEAR|        INTERVAL '-1' YEAR|
+-----------------+-----------------+--------------------------+

Example 5: Overflow results in NULL when ANSI mode is on

>>> import pyspark.sql.functions as sf
>>> origin = spark.conf.get("spark.sql.ansi.enabled")
>>> spark.conf.set("spark.sql.ansi.enabled", "true")
>>> try:
...     spark.range(1).select(sf.try_subtract(sf.lit(-sys.maxsize), sf.lit(sys.maxsize))).show()
... finally:
...     spark.conf.set("spark.sql.ansi.enabled", origin)
+-------------------------------------------------------+
|try_subtract(-9223372036854775807, 9223372036854775807)|
+-------------------------------------------------------+
|                                                   NULL|
+-------------------------------------------------------+