Daniel Agbemava

[Project D] Devlog #3: Reporting Part 2

Daniel Agbemava's photo
Daniel Agbemava
·

2 min read

Before we begin, if you want to get up to speed on where we started, you can find the previous entry in this series here.

I am back to reporting. In the previous devlog, I mentioned that I was going with the "naive" approach. After getting some clarification on the reports from the stakeholders, I now have a clearer picture of how to proceed so I will continue with the naive approach for a little bit and then move to the suggestion we had earlier (i.e, have the data for the report elsewhere so I don't have to do a lot of joins in my main db).

Today, I organised the reports on GitHub so that I could track the implementation of the reports easily. I then went on to start the implementation.

The reports are of two kinds for each criterion, there's the snapshot at the current time and the snapshot over time.

Snapshot at current time is like total number of apples collected, whereas snapshot over time is total number of apples collected per day.

I had an implementation that I was testing out a couple of days ago but today it stuck out to me as odd.

So when we're storing dates for our entities, the time factor is relevant because we need to be able to track it down to the very nanosecond the request was made and the likes. However, when we're requesting the data, we only need the date component, not the time component. So I did a test and indeed, the initial implementation was wrong. The way it was implemented, it was going to be based on the timestamp so it wouldn't return the accurate count for the data.

The query looked like this initially

 @Query("select ...customType" +
            "from Entity e where <some clause> " +
            "group by ..., e.date")

Date in this case is a timestamp, and since we need only the date portion, this would not work.

After playing around and investigating a few solutions, I decided to use the date_trunc function to truncate the date so that I could get the accurate count for the day. So now the query looks something like this

@Query("select ...customType" +
            "from Entity e where <some clause> " +
            "group by ..., date_trunc(e.date)")

For now, this is fine as my database is postgres and I don't see myself changing the database anytime soon. If I have to, I will revisit this query again in the future.

That's all for now, until next time, I bid you adieu.

Javabackenddevlog