How to round timestamp to 10 minutes in Spark 3.0?

Question

I have a timestamp like that in $"my_col":

2022-01-21 22:11:11

with date_trunc("minute",($"my_col"))

2022-01-21 22:11:00

with date_trunc("hour",($"my_col"))

2022-01-21 22:00:00

What is a Spark 3.0 way to get

2022-01-21 22:10:00

?

Answer 1

Convert the timestamp into seconds using unix_timestamp function, then perform the rounding by dividing by 600 (10 minutes), round the result of division and multiply by 600 again:

val df = Seq(
  ("2022-01-21 22:11:11"),
  ("2022-01-21 22:04:04"),
  ("2022-01-21 22:19:34"),
  ("2022-01-21 22:57:14")
).toDF("my_col").withColumn("my_col", to_timestamp($"my_col"))

df.withColumn(
  "my_col_rounded",
  from_unixtime(round(unix_timestamp($"my_col") / 600) * 600)
).show

//+-------------------+-------------------+
//|my_col             |my_col_rounded     |
//+-------------------+-------------------+
//|2022-01-21 22:11:11|2022-01-21 22:10:00|
//|2022-01-21 22:04:04|2022-01-21 22:00:00|
//|2022-01-21 22:19:34|2022-01-21 22:20:00|
//|2022-01-21 22:57:14|2022-01-21 23:00:00|
//+-------------------+-------------------+

---

You can also truncate the original timestamp to hours, get the minutes that your round to 10 and add them to truncated timestamp using interval:

df.withColumn(
  "my_col_rounded",
  date_trunc("hour", $"my_col") + format_string(
    "interval %s minute",
    expr("round(extract(MINUTE FROM my_col)/10.0)*10")
  ).cast("interval")
)