Support for DateTime([timezone]) time fields consistently

[oplehto]

It seems like in certain cases DateTime values with a timezone i.e. (DateTime('America/Chicago')) are not considered valid. For basic timeseries and tables they work but in some cases they fail.

Examples that I found:

• Timestamp column for the logs panel
• Lookup of default timestamp values. Fix here should be as easy as adjusting this query with something likesplitByChar('(',type)[1] AS type?. At the same time it could be optimized with a GROUP BY type, name to return only distinct values

  clickhouse-grafana/src/views/ConfigEditor/FormParts/DefaultValues/DefaultValues.tsx

  Line 5 in a521a07

  const TABLES_QUERY = "SELECT name,database,table,type FROM system.columns WHERE type LIKE 'Date32%' OR type LIKE 'DateTime64%' OR type = 'UInt32' OR match(type,'^DateTime$|^DateTime\\\\([^)]+\\\\)$') OR match(type,'^Date$|^Date\\([^)]+\\)$') ORDER BY type,name FORMAT JSON";

There are probably other situations as well. Seems like some type matching is happening where only DateTime and DateTime64(N) are considered timestamps.

While doing this it should be ensured that the timezone-enabled timestamps work correctly and do not get skewed (i.e. timezone shift done twice accidentally so a GMT+4 becomes GMT+8)

[Slach]

dropdown should be fixed in 6ea625b

[Slach]

@oplehto i tried to reproduce
look #638

I created table which contains DateTime('Europe/Moscow') server in UTC and create dashboard
which use this field as DateTime

I don't see any issues

WHERE $timeFilter works correctly
used time range always in timestamp as int and properly converted from browser timezone to field timezone

Could you provide more concrete examples for your issue?

[oplehto]

An example here would be the timestamp column for the logs panel (in the logs mode) as I mentioned in the issue. I assume you are testing with a Graph or Table panel?

I think it's due to the very specific matching done here for the column type:

clickhouse-grafana/src/datasource/sql_series.ts

Lines 474 to 484 in 0ad4543

	case 'Date':
	case 'DateTime':
	case 'DateTime64':
	case 'DateTime64(3)':
	case 'DateTime64(6)':
	case 'Nullable(Date)':
	case 'Nullable(DateTime)':
	case 'Nullable(DateTime64)':
	case 'Nullable(DateTime64(3))':
	case 'Nullable(DateTime64(6))':
	return FieldType.time;

There may be issues with other panel types as well. I didn't look. Hence this request to review that the timestamp with timezone support is comprehensive meaning all the panel types support it.