CONVERT_TZ and windows

ksqlDB
1

When using the new CONVERT_TZ function for timestamps, how will windowed queries align when creating and intermediate stream with a converted timestamp and creating a CTAS with a window on that?

Previously they were aligned to the epoch.
For example, when creating day windows, they were UTC days.
We had to create a custom UDF (very similar to the CONVERT_TZ) for offsetting the bigint epoch to our desired time zone.

Can we now replace that with the built in function?

thanks

1 Like
2

The CONVERT_TZ function converts TIMESTAMP typed data.

ksqlDB records store time as BIGINT and does not currently support windowing using the TIMESTAMP type, so in order to use CONVERT_TZ, you would have to convert the BIGINT to TIMESTAMP using the FROM_UNIXTIME function and then convert the result back to BIGINT using the UNIX_TIMESTAMP function.

2 Likes
3

This would mean:

SELECT
  TRACKING_DATA[1]->EVENT->TS `event_tsstr`,
  PARSE_TIMESTAMP(TRACKING_DATA[1]->EVENT->TS, 'yyyy-MM-dd''T''HH:mm:ss.SSSX') `event_time`,
  CONVERT_TZ(PARSE_TIMESTAMP(TRACKING_DATA[1]->EVENT->TS, 'yyyy-MM-dd''T''HH:mm:ss.SSSX'), 'UTC', 'Europe/Berlin') `ts_with_offset`,
  UNIX_TIMESTAMP(CONVERT_TZ(PARSE_TIMESTAMP(TRACKING_DATA[1]->EVENT->TS, 'yyyy-MM-dd''T''HH:mm:ss.SSSX'), 'UTC', 'Europe/Berlin')) `epoch_with_offset`,
  TRACKING_DATA[1]->IDENTIFIERS[1]->ID `usage_id`
FROM MY_EVENTS emit changes limit 1;

with TRACKING_DATA[1]->EVENT->TS being our input time string in a format like 2021-04-28T12:29:00.000+0200

4

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.