1
 spark.sql("select case when trim(map('OPT OUT',1,'OPT IN',0,'',0)[coalesce(upper(program_1),'')]) == trim(num_fg) then trim(from_unixtime(unix_timestamp(upd_dt,'MM/dd/yyyy HH:mm:ss.SSS'), 'yyyy-MM-dd HH:mm:ss.sss')) else Now() end as upd_dt from input").show(false)

Input

val sample = Seq(("OPT OUT","1","07/21/2020 09:09:09.382")).toDF("program_1","num_fg", "upd_dt")

In the above written query the micro seconds 'sss' is not returning the input which we are giving.

If the input is 07/21/2020 09:09:09.382 it is returning 07/21/2020 09:09:09.009 but the expected result is 07/21/2020 09:09:09.382 [Whatever microsecond we are giving in input it should display in output].

Oli
  • 9,766
  • 5
  • 25
  • 46
Only developer
  • 61
  • 1
  • 8
  • Does this answer your question? [Can unix\_timestamp() return unix time in milliseconds in Apache Spark?](https://stackoverflow.com/questions/42237938/can-unix-timestamp-return-unix-time-in-milliseconds-in-apache-spark) – ggordon Nov 03 '21 at 10:09
  • I think No, because they have use withcoumn in a data frame and the format of mine is CASE WHEN THEN ELSE END. I'm not sure how to indulge in my format. could you please alter the same in my query?@ggordon – Only developer Nov 05 '21 at 04:13

1 Answers1

0

This will help

 import java.sql.Timestamp
 import java.time.LocalDateTime
 import java.time.format.DateTimeFormatter

 def dateWithMilliSeconds (rawDate:String) = {
  val format = DateTimeFormatter.ofPattern("MM/dd/yyyy HH:mm:ss.SSS")
  Timestamp.valueOf(LocalDateTime.parse(rawDate.trim,format))
 }

spark.udf.register("dateWithMilliSeconds",dateWithMilliSeconds _)

spark.sql("select 
             case when trim(map('OPT OUT',1,'OPT IN',0,'',0 [coalesce(upper(program_1),'')]) == trim(num_fg) 
             then dateWithMilliSeconds(upd_dt) 
             else Now() end as upd_dt 
           from input
           ").show(false)

OUTPUT:
+-----------------------+
|upd_dt                 |
+-----------------------+
|2020-07-21 09:09:09.382|
+-----------------------+
Makhneet Bhatia
  • 16
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Nov 06 '21 at 02:24