Postgres: TIMESTAMP WITH TIME ZONE not parsed

[dflss]

-- If this is a parsing or linting issue, please include a minimal SQL example which reproduces the issue, along with the sqlfluff parse output, sqlfluff lint output and sqlfluff fix output when relevant. Finally, please add any labels that are related to the issue before submitting so that it can be addressed easily! --

Expected Behaviour

I would expect statements with keyword 'timestamp with time zone' to be parsed by sqlfluff, as they are supported by ANSI SQL:2011 standard.

Observed Behaviour

$ sqlfluff lint test.sql --dialect postgres
== [test.sql] FAIL
L: 1 | P: 18 | L012 | Implicit aliasing of column not allowed. Use explicit
| AS clause.
L: 1 | P: 18 | L029 | Keywords should not be used as identifiers.
L: 1 | P: 22 | PRS | Line 1, Position 22: Found unparsable section: " time
| zone '2005-04-02 12:00:00-07' + in..."

$ sqlfluff parse test.sql --dialect postgres
[L: 1, P: 1] |file:
[L: 1, P: 1] | statement:
[L: 1, P: 1] | select_statement:
[L: 1, P: 1] | select_clause:
[L: 1, P: 1] | keyword: 'SELECT'
[L: 1, P: 7] | [META] indent:
[L: 1, P: 7] | whitespace: ' '
[L: 1, P: 8] | select_clause_element:
[L: 1, P: 8] | column_reference:
[L: 1, P: 8] | identifier: 'timestamp'
[L: 1, P: 17] | whitespace: ' '
[L: 1, P: 18] | alias_expression:
[L: 1, P: 18] | identifier: 'with'
[L: 1, P: 22] | unparsable: !! Expected: 'Nothing...'
[L: 1, P: 22] | whitespace: ' '
[L: 1, P: 23] | raw: 'time'
[L: 1, P: 27] | whitespace: ' '
[L: 1, P: 28] | raw: 'zone'
[L: 1, P: 32] | whitespace: ' '
[L: 1, P: 33] | raw: "'2005-04-02 12:00:00-07'"
[L: 1, P: 57] | whitespace: ' '
[L: 1, P: 58] | raw: '+'
[L: 1, P: 59] | whitespace: ' '
[L: 1, P: 60] | raw: 'interval'
[L: 1, P: 68] | whitespace: ' '
[L: 1, P: 69] | raw: "'1 day'"
[L: 1, P: 76] | [META] dedent:
[L: 1, P: 76] | statement_terminator: ';'
[L: 1, P: 77] | newline: '\n'
==== parsing violations ====
L: 1 | P: 22 | PRS | Line 1, Position 22: Found unparsable section: " time zone
| '2005-04-02 12:00:00-07' + in..."

Steps to Reproduce

Create a sql file with the following query:

SELECT timestamp with time zone '2005-04-02 12:00:00-07' + interval '1 day';

Version

sqlfluff version 0.6.0a2
Python 3.8.1

This example comes from the official PostgreSQL documentation.

As per ANSI SQL:2011 standard, the ANSI DateTime data types include TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE, so this should probably be added to the ansi module.

[Olaktal]

+1 same behavior in Redshift with following query

SELECT started_at::timestamptz AT TIME ZONE 'UTC' AS started_at from tmp_table

is wrongly fixed to

SELECT started_at::timestamptz AS at TIME ZONE 'UTC' AS started_at from tmp_table,

[Olaktal]

Hello @WittierDinosaur
I juste tried release 0.6.5 and it didn't solve my problem.
This is my sql test

SELECT
    abc
    , status
    , started_at::timestamptz                           AT TIME ZONE 'UTC' AS started_at
    , ended_at::timestamptz                             AT TIME ZONE 'UTC' AS ended_at
    , created_at::timestamptz                           AT TIME ZONE 'UTC' AS created_at
    , database_reference
FROM hello
WHERE received_date >= '{{ var("received_date") }}'

I ran poetry run sqlfluff fix test.sql and the result is

SELECT
    abc,
    status,
    started_at::timestamptz AS at TIME ZONE 'UTC' AS started_at,
    ended_at::timestamptz AS at TIME ZONE 'UTC' AS ended_at,
    created_at::timestamptz AS at TIME ZONE 'UTC' AS created_at,
    database_reference
FROM hello
WHERE received_date >= '{{ var("received_date") }}'

FYI

poetry run sqlfluff --version
sqlfluff, version 0.6.5

Any idea ?

[WittierDinosaur]

@Olaktal There are a few things going on here:

• TIMESTAMP WITH TIME ZONE parses correctly.
• TIMESTAMP AT TIME ZONE isn't supported as of 0.6.5, but I've added support for it, and it will be available in 0.6.6
• timestamptz is only supported as a data type identifier without the TIME ZONE stuff - it's a bug and a simple one to fix

Can you post your issue as a new issue so that I can keep track of it?

[tunetheweb]

Actually was just looking @WittierDinosaur and have a fix for adding TIMESTAMPTZ to save you. As you say it's very simple!

[tunetheweb]

Those remaining pieces have been implemented now @Olaktal . Will be fixed in the next release.

[Olaktal]

Thank you both @tunetheweb and @WittierDinosaur, can't wait for 0.6.6 !
If you don't hear of me again after this release, it will be good news :)

[tunetheweb]

That might be very soon... Already talking about it despite 0.6.5 only being released last week since we've been knocking out a load of stuff at rate of knots and a good load of stuff fixed since then.

[Olaktal]

Hello guys
I juste tried 0.6.6 and it is not working as expected

input

SELECT
    abc,
    status,
    started_at::timestamptz AT TIME ZONE 'UTC' AS started_at,
    ended_at::timestamptz AT TIME ZONE 'UTC' AS ended_at,
    created_at::timestamptz AT TIME ZONE 'UTC' AS created_at,
    database_reference
FROM hello
WHERE received_date >= '{{ var("received_date") }}'

Running poetry run sqlfluff fix
I got the following warningn: 3 unfixable linting violations found

Result

SELECT
    abc,
    status,
    started_at::timestamptz AS at TIME ZONE 'UTC' AS started_at,
    ended_at::timestamptz AS at TIME ZONE 'UTC' AS ended_at,
    created_at::timestamptz AS at TIME ZONE 'UTC' AS created_at,
    database_reference
FROM hello
WHERE received_date >= '{{ var("received_date") }}'

▶ poetry run sqlfluff --version
sqlfluff, version 0.6.6

FYI, when a try poetry run sqlfluff lint i get

▶ poetry run sqlfluff lint
== [workflow/queries/dbt/models/fhir_api/test_appointment.sql] FAIL
L:   4 | P:  32 | L029 | Keywords should not be used as identifiers.
L:   4 | P:  34 |  PRS | Line 4, Position 34: Found unparsable section: " TIME
                       | ZONE 'UTC' AS started_at"
L:   5 | P:  30 | L029 | Keywords should not be used as identifiers.
L:   5 | P:  32 |  PRS | Line 5, Position 32: Found unparsable section: " TIME
                       | ZONE 'UTC' AS ended_at"
L:   6 | P:  32 | L029 | Keywords should not be used as identifiers.
L:   6 | P:  34 |  PRS | Line 6, Position 34: Found unparsable section: " TIME
                       | ZONE 'UTC' AS created_at"

Any idea why it's not parsable ?

cc @tunetheweb

[barrywhart]

@Olaktal: You need to specify --dialect postgres. The default is to use the ANSI dialect.