Interval type does not accept non-literal value

[agentm]

The interval data type, as implied by ilLiteral, does not support scalar expressions. Is this a limitation of SQL2011?

This basic expression fails to parse:

SELECT INTERVAL t.numeric_field HOUR FROM t;

[JakeWheat]

It looks like it isn't allowed in ANSI SQL, here is the relevant grammar from the standard:

<interval literal> ::=
  INTERVAL [ <sign>  ] <interval string>  <interval qualifier> 

<interval string> ::=
  <quote>  <unquoted interval string>  <quote> 

Did you see a SQL system supporting this syntax with expressions?

[agentm]

We are using BigQuery with this convenience.

PostgreSQL interval syntax is definitely non-standard as it accepts a string argument but I can't even make it accept concatenated strings:

select interval ('1 ' || 'hour');

I worked around this by allowing the IntervalLit type to take a ScalarExpr (instead of a String) and parse it with idenExpr.

We'll likely have other changes for BigQuery support in the pipeline.

[JakeWheat]

There is a straightforward alternative which I think is standard, and you can use any expression in it:

select cast('1 hour' as interval);

[JakeWheat]

Here's a little follow up on this syntax. I think Postgres's interval syntax is (mostly) standard. This interval syntax in ANSI can only be used to create literals, it is not a general interval constructor.

This is a postgres extension as far as I can tell:

select interval '1 hour';

This is the ANSI literal syntax, which postgres also supports.

select interval '1' hour;

Both of these are literals, and you can only put a literal string to them, not a string valued expression. This is ANSI standard behaviour for the second example. '1 ' || 'hour' is an expression, and would only work anywhere in the syntax where an expression is allowed, and never where only a string literal is allowed.

There doesn't appear to be any constructor for intervals in Postgres or ANSI, you have to do some variation of converting to a string then parsing it. As you mentioned, BigQuery has filled this gap by extending the literal syntax to be a general constructor:

select interval an_expression hour;

It should be trivial to add support for this syntax in simple-sql-parser if desired.

[lorenzo]

In mysql this is valid too: select * where date >= (now() - interval 3 DAY) (there are no quotes around the number.