amazon-web-servicesamazon-qldb

How to get latest result set based on the timestamp in amazon qldb?

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?

Solution

  • 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.