ververica/flink-sql-cookbook

Mssing info for handling time zone

O1lpunch3r opened this issue · 4 comments

O1lpunch3r commented

Hi there,

thank you for this great examples for Flink SQL. It helps a lot for getting useful tips.

I am missing some information about time zones in this post. I have in a csv file a date as string (without zone identifier). But I know that UTC is meant. In Flink SQL I would create the timestamp from the string using this function:

eventtime AS TO_TIMESTAMP(message_date, 'yyyy/MM/dd HH:mm:ss')

How can I specify that this string should be interpreted as UTC?

morsapaes commented

Hi, @O1lpunch3r! Thanks for the nice words, we're glad that you're enjoying the cookbook. 🍪

Enforcing and/or manipulating timezones is a bit tricky at the moment, because TIMESTAMP WITH TIMEZONE is not supported as a date type yet 1. With the current type system, what you could do as a workaround would be to cast the string as TIMESTAMP WITH LOCAL TIMEZONE, which defaults to UTC (although this requires you to format the input string as yyyy-mm-dd HH:mm:ss (ISO-8601) first). Something like: CAST(eventtime AS TIMESTAMP(3) WITH LOCAL TIME ZONE).

1 For reference, this will be worked on in FLINK-20869.

O1lpunch3r commented

Thank you for opening an issue.

Unfortunately I have no possibility to change the datestring of the data.

I tried via TableConfig to change the behavior but without success:

tableEnv.getConfig.setString("table.local-time-zone", "UTC")

O1lpunch3r commented

And in Flink SQL (1.11) you cannot declare a WATERMARK definition on a TIMESTAMP(3) WITH LOCAL TIME ZONE field. It has to be TIMESTAMP.

morsapaes commented

Yeah, the table.local-time-zone configuration option doesn't help in this case, as you still have a data type that doesn't store any timezone information (although it'd affect the conversion).

You could change the string format on the Flink side with some ugly nesting of conversions, but as you've pointed out this ends up not being useful because you can't define a watermark on a TIMESTAMP WITH LOCAL TIME ZONE column. Although I don't see other alternatives for now, I'll bring it up as a Flink SQL usability issue for the next release.

Thanks a lot for reporting it!