在Presto中从变量生成间隔

In Presto SQL, unlike regular SQL, intervals must be created with inverted commas:

在Presto SQL中,与常规SQL不同,必须使用引号括起来创建间隔:

INTERVAL '1' DAY

rather than

INTERVAL 1 DAY

I am trying to generate a set of dates as described here: https://*.com/a/2157776/2388930, but am encountering an issue with

我正在尝试生成一组日期,如下所述:https://*.com/a/2157776/2388930,但遇到问题

INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY

How might this be achieved? I tried

怎么可能实现这一目标?我试过了

parse((a.a + (10 * b.a) + (100 * c.a)) as varchar)

but this was not successful.

但这没有成功。

2 个解决方案

#1

This is not a direct answer to the question, but if the goal is to replicate the results described in the linked Stack Overflow question, generate days from date range, here is an alternative method to generate a sequence of dates in Presto:

这不是问题的直接答案,但如果目标是复制链接的Stack Overflow问题中描述的结果,从日期范围生成天数,这里是在Presto中生成日期序列的另一种方法:

SELECT
    CAST(date_column AS DATE) date_column
FROM
    (VALUES
        (SEQUENCE(FROM_ISO8601_DATE('2010-01-20'), 
                  FROM_ISO8601_DATE('2010-01-24'), 
                  INTERVAL '1' DAY)
        )
    ) AS t1(date_array)
CROSS JOIN
    UNNEST(date_array) AS t2(date_column)
;

Output:

 date_column
-------------
 2010-01-20
 2010-01-21
 2010-01-22
 2010-01-23
 2010-01-24

You can also use other INTERVAL values besides DAY and different step sizes besides '1'.

您还可以使用除DAY之外的其他INTERVAL值以及除“1”之外的不同步长。

*Adapted from this issue comment, https://github.com/prestodb/presto/issues/2169#issuecomment-68521569.

*改编自本期评论,https://github.com/prestodb/presto/issues/2169#issuecomment-68521569。

#2

I ended up using date_add:

我最终使用date_add:

date_add('day', -(a.a + (10 * b.a) + (100 * c.a)), date_trunc('day', now()))

#1