How to convert ISO 8601 duration to seconds

user_7631188 0
Viewed 511

I have a column in spark dataframe as time_span values are in iso 8601 duration ex: P0Y0M0DT0H5M35S . I want to convert that values in to seconds. Is there a function in spark or Scala which will help me do that? I am looking for a way and was unsuccessful I tried with duration

import java.time.Duration
java.time.Duration.parse("P0Y0M0DT0H5M35S")

This gives me err as:

java.time.format.DateTimeParseException: Text cannot be parsed to a Duration

Am I doing anything wrong in passing value to function. I found this documentation https://docs.oracle.com/javase/8/docs/api/java/time/Duration.html

If I was successful in doing it this way then will have to apply additional logic to do it on whole dataframe column

apache-spark scala java
2 Answers

hope the below approach helps you.

import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._

val isoToSecondsUDF = udf( (value: String) => (java.time.Duration.parse("PT".concat(value.split("T")(1))).get(java.time.temporal.ChronoUnit.SECONDS)))

val df=Seq(("P0Y0M0DT0H5M35S")).toDF("value")

df.withColumn("seconds",isoToSecondsUDF($"value")).show()
/*
+---------------+-------+
|          value|seconds|
+---------------+-------+
|P0Y0M0DT0H5M35S|    335|
+---------------+-------+
*/
user_61808300

Updated Solution to cover case where month and day is present for eg: P0Y0M2DT23H59M56S. and P0Y1M2DT23H59M56S

We will need to use time4j lib : https://github.com/MenoData/Time4J

Here is code :

import org.apache.spark.sql.types._
import org.apache.spark.sql.functions._
import  net.time4j.Duration


def getSeconds(value: String) : String={
var b = Duration.parsePeriod(value).toTemporalAmount().get(java.time.temporal.ChronoUnit.MONTHS)
var c = Duration.parsePeriod(value).toTemporalAmount().get(java.time.temporal.ChronoUnit.DAYS)
var days =((b*30)+c).toString()
var seconds = (java.time.Duration.parse("P".concat(days).concat("DT").concat(if(value.contains("T")) value.split("T")(1) else value.split("D")(1))).get(java.time.temporal.ChronoUnit.SECONDS)).toString()
return seconds
}
val isoToSecondsUDF = udf( (value: String) => getSeconds(value))
spark.udf.register("isoToSecondsUDF", isoToSecondsUDF)
val df=Seq(("P0Y0M2DT23H59M56S")).toDF("value")
df.withColumn("seconds",isoToSecondsUDF($"value")).show()

First get the number of months then convert to days and add it to existing number of days then pass that to parse method. @sathya

Output:

+-----------------+-------+
|            value|seconds|
+-----------------+-------+
|P0Y0M2DT23H59M56S| 259196|
+-----------------+-------+

+-----------------+-------+
|            value|seconds|
+-----------------+-------+
|P0Y1M2DT23H59M56S|2851196|
+-----------------+-------+
user_76311880
Related