1

I have many an IonStruct as follows.

{
  revenueId: "0dcb7eb6-8cec-4af1-babe-7292618b9c69",
  ownerId: "u102john2021",
  revenueAddedTime: 2020-06-20T19:31:31.000Z,

}

I want to write a query to select the latest records set within a given year.

for example, suppose I have a set of timestamps like this - A - 2019-06-20T19:31:31.000Z B - 2020-06-20T19:31:31.000Z C - 2020-06-20T19:31:31.000Z D - 2021-07-20T19:31:31.000Z E - 2020-09-20T19:31:31.000Z F - 2020-09-20T19:31:31.000Z

If the selected year is between 2020 and 2021, I want to return records which having the latest timestamp. in this case. E and F,

I tried many ways like

"SELECT * FROM REVENUES AS r WHERE r.ownerId = ? AND r.revenueAddedTime >= ? AND r.revenueAddedTime < ?"

Can anyone help me here?

Dasun
  • 602
  • 1
  • 11
  • 34

1 Answers1

1

Although I have no experience in qldb syntax, it seems to have similar properties to other db syntax in the sense that you can format your timestamps using these doc:

https://docs.aws.amazon.com/qldb/latest/developerguide/ql-functions.timestamp-format.html

https://docs.aws.amazon.com/qldb/latest/developerguide/ql-functions.to_timestamp.html

Once you format the timestamp, you may be able to do the > and < query syntax.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Mike Beck
  • 350
  • 1
  • 8
  • Actually, why should I format the time stamp? My problem is to get the latest record set for the given year. with > < how can i do that? – Dasun Jul 19 '21 at 16:15