Spark上用Scala实现MySQL中CONVERT_TZ函数的功能

Spark上用Scala实现MySQL中CONVERT_TZ函数的功能

import java.sql.Timestamp
import java.text.SimpleDateFormat
import java.util.{Date, TimeZone}

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions.udf

 val _pattern = "yyyy-MM-dd HH:mm:ss"
 val _fromFormatter = new SimpleDateFormat(_pattern)
 val _toFormatter = new SimpleDateFormat(_pattern)

 def _convertTz(_dateTime: Any, _fromTz: String, _toTz: String): String = {
   _fromFormatter.setTimeZone(TimeZone.getTimeZone(_fromTz))
   _toFormatter.setTimeZone(TimeZone.getTimeZone(_toTz))

   _dateTime match {
     case str: String =>
       _toFormatter.format(_fromFormatter.parse(str.substring(0, _pattern.size)))
     case l: Long =>
       _toFormatter.format(new Date(l))
     case i: Integer =>
       _toFormatter.format(new Date(i * 1000L))
     case t: Timestamp =>
       _toFormatter.format(new Date(t.getTime))
     case _ =>
       null
   }
 }

 spark.udf.register("CONVERT_TZ", udf(_convertTz(_: Any, _: String, _: String)))
 spark.sql("SELECT CONVERT_TZ(UC.CREATED_TIME,'GMT','GMT+8') FROM USER_COUPON UC LIMIT 10").show()