Malloy no longer accepts TIMESTAMP WITH TIME ZONE

[popmonkey]

sometime in the last few versions Malloy stopped supporting DuckDB's TIMESTAMPTZ (aka TIMESTAMP WITH TIME ZONE) column type.

maybe related to work here: https://github.com/malloydata/malloy/issues/791 ?

table foo uses TIMESTAMPTZ, table foo2 uses TIMESTAMP

source: foo is table('duckdb:/tmp/foo.parquet')
source: foo2 is table('duckdb:/tmp/foo2.parquet')

foo.parquet.gz foo2.parquet.gz

[popmonkey]

parquet-tools inspect for foo.parquet:

type only:

physical_type: INT64
logical_type: Timestamp(isAdjustedToUTC=true, timeUnit=microseconds, is_from_converted_type=false, force_set_converted_type=false)
converted_type (legacy): TIMESTAMP_MICROS

full dump

> parquet-tools inspect /tmp/foo.parquet                           

############ file meta data ############
created_by: DuckDB
num_columns: 2
num_rows: 1
num_row_groups: 1
format_version: 1.0
serialized_size: 201


############ Columns ############
ts
num

############ Column(ts) ############
name: ts
path: ts
max_definition_level: 1
max_repetition_level: 0
physical_type: INT64
logical_type: Timestamp(isAdjustedToUTC=true, timeUnit=microseconds, is_from_converted_type=false, force_set_converted_type=false)
converted_type (legacy): TIMESTAMP_MICROS
compression: SNAPPY (space_saved: -6%)

############ Column(num) ############
name: num
path: num
max_definition_level: 1
max_repetition_level: 0
physical_type: INT32
logical_type: Int(bitWidth=32, isSigned=true)
converted_type (legacy): INT_32
compression: SNAPPY (space_saved: -7%)

[lloydtabb]

can you try ts::timestamp @mtoy-googly-moogly any ideas?

[popmonkey]

ts::timestamp works - thanks! seems like just a matter of recognizing the type

[mtoy-googly-moogly]

Malloy doesn't grok the "TIMESTAMP WITH TIME ZONE" type, with the timezone work, that will get fixed. Casting it to timestamp will let you use it in Malloy for now.

I don't think we ever worked with data of that type. If you had asked me what we used to do before we supported the idea of an unsupported type, I would have said, "All types that malloy don't support are unavailable. Depending on dialect those columns are either removed from the schema, or replaced in the schema with the constant string The column names 'yourColNam' is of type 'SQL TYPE NAME' which Malloy does not support

::timestamp actually generates a cast. It isn't recognizing the type. The malloy internals do not handle DATETIME data in bigquery and do not handle TSTZ data in postgres and duckdb. Working on fixing that.

[popmonkey]

ty @mtoy-googly-moogly - the same tables worked before so my guess is that 0.4.0 duckdb and 0.7.1 present timestamp with time stamp differently - lots of timestamp related changes in duckdb since 0.4.0

https://github.com/duckdb/duckdb/releases?q=timestamp&expanded=true

looking forward to tz support!