TimeSeries DQL -join

[JhonU]

Good day, I have two time series as shown in the image. The first series is the result of bizevents that were ingested as historical data, and the maketimeseries command was used to generate a time series for the period described from 01/12/2023 to 30/10/2024. The second time series consists of current data from a metric that provides data from 30/10/2024 to the present. My goal is to combine these two results into a single time series.

Currently, my query uses the append command to combine the results, but as you can see, it produces two separate records even though the timeframes are complementary.

fetch bizevents, from: "2023-12-01T00:00:00.000-0500", to:now()
| filter tipo =="cloudfront_requests_sum_by_region" 
| fieldsAdd timestamp=toTimestamp(timeantiguo)+5h 
| fieldsRemove event.type, event.provider, event.id, dt.openpipeline.pipelines, dt.openpipeline.source
| makeTimeseries Request1=sum(value), interval:24h, from: toTimestamp("2023-12-01T00:00:00.000-0500"), to:toTimestamp("2024-10-30T00:00:00.000-0500")
| append [
timeseries Request1=sum(cloud.aws.cloudfront.requests_sum_by_region), interval:24h, from: "2024-10-30T00:00:00.000-0500", to:now()
]

[krzysztof_hoja · Accepted solution]

There are few ways to achieve it, but probably the easiest one is via associating timestamp with each of elements of produced timeseries (use of start() function and building array of records), "unwrapping" them and building single timeseries one again. 

fetch bizevents, from: "2023-12-01T00:00:00.000-0500", to:now()
| filter tipo =="cloudfront_requests_sum_by_region" 
| fieldsAdd timestamp=toTimestamp(timeantiguo)+5h 
| makeTimeseries {Request1=sum(value), timestamp=start()}, interval:24h, from: toTimestamp("2023-12-01T00:00:00.000-0500"), to:toTimestamp("2024-10-30T00:00:00.000-0500")
| append [
timeseries {Request1=sum(cloud.aws.cloudfront.requests_sum_by_region), timestamp=start()}, interval:24h, from: "2024-10-30T00:00:00.000-0500", to:now()
]

| fieldsAdd d=record(timestamp=timestamp[], Request1=Request1[])
| expand d 
| makeTimeseries Request1=sum(d[Request1]), time:d[timestamp], interval:24h, from: toTimestamp("2023-12-01T00:00:00.000-0500"), to: now()

Possible alternatives is via:

| summarize Request1=sum(Request1[]), by: {timeframe, interval}

but this requires aligning timeframes first and making Request1 arrays of the same lengths and placing values in time relevant slots. This could be achieved by using exactly the same timeframes in source queries (fetch bizevents and timeframes) and  then zeroing values if one of arrays if there is overlap