CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
stack0114104
stack0114104

Reputation: 87

How to convert from_unixtime to to_utc_timestamp in the same dataframe without adding new columns?

var columnnames= "callStart_t,callend_t" // Timestamp column names are dynamic input. 

 scala> df1.show()
+------+------------+--------+----------+
|  name| callStart_t|personid| callend_t|
+------+------------+--------+----------+
| Bindu|1080602418  |       2|1080602419|
|Raphel|1647964576  |       5|1647964576|
|   Ram|1754536698  |       9|1754536699|
+------+------------+--------+----------+

code which i tried : 

val newDf = df1.withColumn("callStart_Time", to_utc_timestamp(from_unixtime($"callStart_t"/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))

 val newDf = df1.withColumn("callend_Time", to_utc_timestamp(from_unixtime($"callend_t"/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))

Here, I don't want new columns to convert (from_unixtime to to_utc_timestamp), the existing column itself I want to convert

Example Output 

+------+---------------------+--------+--------------------+
|  name| callStart_t         |personid| callend_t          |
+------+---------------------+--------+--------------------+
| Bindu|1970-01-13 04:40:02  |       2|1970-01-13 04:40:02 |
|Raphel|1970-01-20 06:16:04  |       5|1970-01-20 06:16:04 |
|   Ram|1970-01-21 11:52:16  |       9|1970-01-21 11:52:16 |
+------+---------------------+--------+--------------------+

Note: The Timestamp column names are dynamic.

how to get each column dynamically?

Upvotes: 0

Views: 909

Answers (1)

Assaf Mendelson
Assaf Mendelson

Reputation: 13001

Just use the same name for the column and it will replace it:

val newDf = df1.withColumn("callStart_t", to_utc_timestamp(from_unixtime($"callStart_t"/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))
val newDf = df1.withColumn("callend_t", to_utc_timestamp(from_unixtime($"callend_t"/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))

To make it dynamic, just use the relevant string. For example:

val colName = "callend_t"
val newDf = df.withColumn(colName , to_utc_timestamp(from_unixtime(col(colName)/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))

For multiple columns you can do:

val columns=Seq("callend_t", "callStart_t")
val newDf = columns.foldLeft(df1){ case (curDf, colName) => curDf.withColumn(colName , to_utc_timestamp(from_unixtime(col(colName)/1000,"yyyy-MM-dd hh:mm:ss"),"Europe/Berlin"))}

Note: as stated in the comments, the division by 1000 is not needed.

Upvotes: 1

Related Questions