Getting array_agg values from single table with different conditions

Question

I have timeseries database using TimeScaleDB extension on Postgres for managing the OHLCV values of different resolutions on the tick data.

Aggregation, continues aggregation, materialized view are all working fine. As part of optimization, I need to extract the data from materialized view in my standard POJO in order to avoid looping just to map the result to my response object.

Without this, I have to loop through thousands of results to my response object, which is taking more time and also not good performance during peak traffic times.

My Response Object :

@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
public class TVHistoryDataResponse {
    private String s;
    private String errmsg;
    private List<Long> t;
    private List<BigDecimal> o;
    private List<BigDecimal> h;
    private List<BigDecimal> l;
    private List<BigDecimal> c;
    private List<BigDecimal> v;

    public TVHistoryDataResponse(){
        this.c = new ArrayList<>();
        this.o = new ArrayList<>();
        this.h = new ArrayList<>();
        this.l = new ArrayList<>();
        this.v = new ArrayList<>();
        this.t = new ArrayList<>();
    }

Output required:

{
"s":"ok",
"t":[1666447200,1666461600,1666476000,1666490400],
"o":[19214.2,19223.8,19175.0,19195.1],
"h":[19231.3,19236.8,19217.1,19211.1],
"l":[19200.0,19122.0,19164.1,19155.0],
"c":[19223.7,19175.1,19195.2,19188.9],
"v":[5921.40400000014,38568.243999996805,19393.156000002222,18732.651000003843]
}

View Name: one_minute

View Structure :

         day |               market |    high |  open | close | low |  volume
 
"2022-10-22 13:35:00+05:30" "BTCUSDT"   19166.5 19166.4 19165   19165   41.954999999999984
"2022-10-22 13:36:00+05:30" "BTCUSDT"   19165.1 19165.1 19162.7 19162.7 13.671999999999999
"2022-10-22 14:18:00+05:30" "BTCUSDT"   19149.6 19149.6 19149.6 19149.5 5.623999999999998
"2022-10-22 14:19:00+05:30" "BTCUSDT"   19149.6 19149.6 19149.5 19149.5 26.797000000000004

Query I have tried :

@Query(nativeQuery = true,
           value = "SELECT  \n" +
                   "array_agg(extract(epoch from day))as t,\n" +
                   "array_agg(open)as o,\n" +
                   "array_agg(high)as h,\n" +
                   "array_agg(low)as l,\n" +
                   "array_agg(close)as c,\n" +
                   "array_agg(volume)as v FROM one_minute WHERE market=:market AND day>=:start AND day <=:end group BY day ORDER BY day ASC limit 1000;")
    Optional<TVHistoryDataResponse> getAggregatedResponse(
            @Param(value = "market")String market,
            @Param(value = "start")ZonedDateTime start,
            @Param(value = "end")ZonedDateTime end);

Method that calls this Query :

    public TVHistoryDataResponse fetchHistoryData(Market market, long from, long to, String r) {

        TVHistoryDataResponse tvHistoryDataResponse = new TVHistoryDataResponse();
        DateTimeFormatter responseFormatter=DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss.S");

        ZonedDateTime startTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(from), ZoneOffset.systemDefault());
        ZonedDateTime endTime=ZonedDateTime.ofInstant(Instant.ofEpochSecond(to), ZoneOffset.systemDefault());

        Optional<TVHistoryDataResponse> testResponse=tradingDataRepository.getAggregatedResponse(market.name(), startTime, endTime);

Using same Query directly in PgAdmin :

Error :


Getting Null values

Where exactly do you need help? are the results not accurate or are you getting any errors running the actual SQL? — jonatasdp, Nov 09 '22 at 15:33
Oh yes, sorry. I get error. and Also, I guess my query doesn't really map exactly to my POJO. Editing my question with the error details. The very same query runs fine when I use it in PgAdmin Query tool. It results me data. — Gladiator9120, Nov 10 '22 at 04:46
It seems the hybernate is not prepared to work with array of strings or dates or decimals. Probably you'll need to register this data types. Similar issue here: https://stackoverflow.com/questions/21630370/no-dialect-mapping-for-jdbc-type-2003 — jonatasdp, Nov 10 '22 at 14:26
@jonatasdp I resolved that error, now I am getting only null response. I checked DB and I have values in the view between those timestamps — Gladiator9120, Nov 11 '22 at 06:49
I'm not a hibernate user anymore. Last time I used it was in 2006, so, I cannot help too much here. I'd recommend you to create a minimal reproducible example and use the hibernate label to see if you can get help from other advanced hibernate users. If the query is resulting data, probably the error is not at the Timescale part anymore. I wish you good luck with the challenge! — jonatasdp, Nov 11 '22 at 13:11
What is the result running it straight on PG? I mean without hibernate. Does it look like what you expect? — noctarius, Nov 21 '22 at 06:52
@noctarius nope. even in the PgAdmin, it still results in separate rows, but with { } to make it look like JSON, but they aren't grouped together. So I guess there's something wrong with my query itself. — Gladiator9120, Nov 21 '22 at 06:53
@noctarius updated my question with SS of the query result directly from PgAdmin — Gladiator9120, Nov 21 '22 at 07:00
From the time values it seems like your "day" column may not actually be a day. Look at the values, those are often just a few seconds away from each other. Maybe have a look at that. — noctarius, Nov 22 '22 at 08:32
@noctarius sorry for the name. The name of the column should be date_time, confusing. But it's actually 1 minute interval. and not really one_day. — Gladiator9120, Nov 23 '22 at 05:25

Getting array_agg values from single table with different conditions

0 Answers0