How to retrieve all records from postgres table since the last timestamp using sparksql

Question

where i need to retrieve newly fetched records based on timestamp. I used "max" which only gives 1 record, and so is the case with desc and limit

I need to dynamically fetch records when the data loaded into the table

       Task     Task-B    Timestamp
       aaa      bbb       2020-09-02 16:45:12
       aa2      bb2       2020-09-02 17:16:10
       aa3      bb3       2020-09-03 10:09:15
       aa4      bb4       2002-09-01 09:14:34

Task aaa to aa3 are new, i need to retrieve only that

       Task     Task-B    Timestamp
       aaa      bbb       2020-09-02 16:45:12
       aa2      bb2       2020-09-02 17:16:10
       aa3      bb3       2020-09-03 10:09:15

i cannot hard code it, it should be dynamic – sanjana jha Sep 03 '20 at 07:11 — sanjana jha, Sep 03 '20 at 07:11

mvasyliv · Answer 1 · 2020-09-03T10:25:34.517

0

Task: show data last 24 hours.

import org.apache.spark.sql.functions._
import spark.implicits._
    
case class Task(task: String, taskR: String, timestamp: String)

val sourceDF = Seq(Task("aaa", "bbb", "2020-09-02 16:45:12"),
  Task("aa2", "bb2", "2020-09-02 17:16:10"),
Task("aa3", "bb3", "2020-09-03 10:09:15"),
Task("aa4", "bb4", "2002-09-01 09:14:34")
).toDF()

sourceDF.show(false)
//  +----+-----+-------------------+
//  |task|taskR|timestamp          |
//  +----+-----+-------------------+
//  |aaa |bbb  |2020-09-02 16:45:12|
//  |aa2 |bb2  |2020-09-02 17:16:10|
//  |aa3 |bb3  |2020-09-03 10:09:15|
//  |aa4 |bb4  |2002-09-01 09:14:34|
//  +----+-----+-------------------+


val resDF = sourceDF
  .where(((unix_timestamp(current_timestamp()) - unix_timestamp('timestamp)) / 3600) <= 24)

resDF.show(false)
resDF.printSchema()
//  +----+-----+-------------------+
//  |task|taskR|timestamp          |
//  +----+-----+-------------------+
//  |aaa |bbb  |2020-09-02 16:45:12|
//  |aa2 |bb2  |2020-09-02 17:16:10|
//  |aa3 |bb3  |2020-09-03 10:09:15|
//    +----+-----+-------------------+
//
//  root
//  |-- task: string (nullable = true)
//  |-- taskR: string (nullable = true)
//  |-- timestamp: string (nullable = true)

edited Sep 03 '20 at 10:25

answered Sep 03 '20 at 08:24

mvasyliv

1,214
6
10

I like, similarly for pyspark. https://stackoverflow.com/questions/44020818/how-to-calculate-date-difference-in-pyspark – mvasyliv Sep 03 '20 at 09:04
Thanks, i just need 1 days. But its throwing with datatype – sanjana jha Sep 03 '20 at 09:22
do you can show example? SELECT '2018-07-25 10:30:30'::TIMESTAMP::DATE; – mvasyliv Sep 03 '20 at 09:58
I just need to retrieve from current datetime to 24 hours back – sanjana jha Sep 03 '20 at 10:03
Please, see my answer to period last 24 hours – mvasyliv Sep 03 '20 at 10:26
1

@sanjanajha: `where "timestamp" >= current_timestamp - interval '24 hour'` or if you actually mean "today" rather than 24 hours: `where "timestamp" >= current_date` – Sep 03 '20 at 10:28

How to retrieve all records from postgres table since the last timestamp using sparksql

1 Answers1