Spark dataframe change column value to timestamp

Question

I have a jsonl file I've read in, created a temporary table view and filtered down the records that I want to ammend.

val df = session.read.json("tiny.jsonl")
df.createOrReplaceTempView("tempTable")
val filter = df.select("*").where("field IS NOT NULL")

Now I am at the part where I have been trying various things. I want to change a column called "time" with the currentTimestamp before I write it back. Sometimes I will want to change the currentTimestamp to be timestampNow - 5 days for example.

val change = test.withColumn("server_time", date_add(current_timestamp(), -1))

The example above will throw me back a date that's 1 from today, rather than a timestamp.

Edit: Sample Dataframe that mocks out my jsonl input:

  val df = Seq(
    (1, "fn", "2018-02-18T22:18:28.645Z"),
    (2, "fu", "2018-02-18T22:18:28.645Z"),
    (3, null, "2018-02-18T22:18:28.645Z")
  ).toDF("id", "field", "time")

Expected output:

+---+------+-------------------------+
| id|field |time                     |
+---+------+-------------------------+
|  1| fn   | 2018-04-09T22:18:28.645Z|
|  2| fn   | 2018-04-09T22:18:28.645Z|
+---+------+-------------------------+

Please add sample input and expected output. Also, read: https://stackoverflow.com/questions/48427185/how-to-make-good-reproducible-apache-spark-dataframe-examples — philantrovert, Apr 10 '18 at 08:40
@philantrovert I've updated with a sample input and expected — Curious_Bop, Apr 10 '18 at 09:09

koiralo · Accepted Answer · 2018-04-10T09:32:00.793

2

If you want to replace current column time with current timestamp then, you can use current_timestamp function. To add the number of days you can use SQL INTERVAL

val df = Seq(
  (1, "fn", "2018-02-18T22:18:28.645Z"),
  (2, "fu", "2018-02-18T22:18:28.645Z"),
  (3, null, "2018-02-18T22:18:28.645Z")
).toDF("id", "field", "time")
  .na.drop()

  val ddf  = df
    .withColumn("time", current_timestamp())
    .withColumn("newTime", $"time" + expr("INTERVAL 5 DAYS"))

Output:

+---+-----+-----------------------+-----------------------+
|id |field|time                   |newTime                |
+---+-----+-----------------------+-----------------------+
|1  |fn   |2018-04-10 15:14:27.501|2018-04-15 15:14:27.501|
|2  |fu   |2018-04-10 15:14:27.501|2018-04-15 15:14:27.501|
+---+-----+-----------------------+-----------------------+

edited Apr 10 '18 at 09:32

answered Apr 10 '18 at 09:14

koiralo

22,594
6
51
72

How would you amend the current_timestamp so I could say 5 days from today for example? – Curious_Bop Apr 10 '18 at 09:16
Use date_add function to add the number of days in current days – koiralo Apr 10 '18 at 09:18
just like my example? that just returns back a different date rather than a timestamp? – Curious_Bop Apr 10 '18 at 09:22
I'm getting a `Value '$' is not a member of stringContext` error? any ideas? – Curious_Bop Apr 10 '18 at 09:44
did you import as `import session.implicits._` or just use `col("time")` – koiralo Apr 10 '18 at 09:45
brill, thanks very much :) – Curious_Bop Apr 10 '18 at 09:56

Spark dataframe change column value to timestamp

1 Answers1