火花计算动态时间戳间隔

Question

具有带有时间戳列（TIMESTAMP类型）的dataframe，称为" maxtmstmp"和另一个带有小时的列，称为" WindowHours"的整数。我想动态减去时间戳和整数列以获得较低的时间戳。

我的数据和所需效果（" mintmstmp"列）：

+-----------+-------------------+-------------------+
|WindowHours|          maxTmstmp|          minTmstmp|
|           |                   |(maxTmstmp - Hours)|
+-----------+-------------------+-------------------+
|          1|2016-01-01 23:00:00|2016-01-01 22:00:00|
|          2|2016-03-01 12:00:00|2016-03-01 10:00:00|
|          8|2016-03-05 20:00:00|2016-03-05 12:00:00|
|         24|2016-04-12 11:00:00|2016-04-11 11:00:00|
+-----------+-------------------+-------------------+
 root
     |-- WindowHours: integer (nullable = true)
     |-- maxTmstmp: timestamp (nullable = true)

我已经找到了带有小时间隔解决方案的表达式，但这不是动态的。下面的代码无法按预期工作。

standards.
      .withColumn("minTmstmp", $"maxTmstmp" - expr("INTERVAL 10 HOURS"))
      .show()

在Spark 2.4和Scala上操作。

Answer 1

一种简单的方法是将 maxTmstmp转换为unix时间，从中减去WindowHours的值，然后将结果转换回SPARK Timestamp，如下所示：

import java.sql.Timestamp
import org.apache.spark.sql.functions._
import spark.implicits._
val df = Seq(
  (1, Timestamp.valueOf("2016-01-01 23:00:00")),
  (2, Timestamp.valueOf("2016-03-01 12:00:00")),
  (8, Timestamp.valueOf("2016-03-05 20:00:00")),
  (24, Timestamp.valueOf("2016-04-12 11:00:00"))
).toDF("WindowHours", "maxTmstmp")
df.withColumn("minTmstmp",
    from_unixtime(unix_timestamp($"maxTmstmp") - ($"WindowHours" * 3600))
  ).show
// +-----------+-------------------+-------------------+
// |WindowHours|          maxTmstmp|          minTmstmp|
// +-----------+-------------------+-------------------+
// |          1|2016-01-01 23:00:00|2016-01-01 22:00:00|
// |          2|2016-03-01 12:00:00|2016-03-01 10:00:00|
// |          8|2016-03-05 20:00:00|2016-03-05 12:00:00|
// |         24|2016-04-12 11:00:00|2016-04-11 11:00:00|
// +-----------+-------------------+-------------------+