Calcite SQL does not recognize CONVERT_TIMEZONE in Apache Beam

Question

I'm running a simple Sql transform in my Beam pipeline:

SELECT CONVERT_TIMEZONE('America/New York', 'UTC',(TIMESTAMP '1970-01-01 00:00:00' + OriginalTZ * INTERVAL '1' SECOND)) as MyUTC FROM PCOLLECTION

But i get the error:

 No match found for function signature CONVERT_TIMEZONE(, , )

But according to the Calcite documentation, that's how the syntax is.

I'm using Apache Beam version 2.29. But it seems that the Calcite sql version within is an old version 2.20 from 2019.

How can I use the CONVERT_TIMEZONE in my Calcite sql?

user_3175889 · Answer

Apache Beam is currently on Calcite SQL 1.20 while CONVERT_TIMEZONE was added to 1.21.

A workaround is to use a normal ParDo or Map to convert the timezone. If you are using Python, some examples: Python Timezone conversion.

user_949327 · Answer

However, you can use SqlTransform#registerUdf to add your own (and you can use Calcite's Apache 2.0 licensed code exactly if you want to).