在 presto SQL 中将 int 格式化为 date

Question

我有一个整数日期列"date_created"存储诸如...

20180527, 20191205, 20200208

我

想知道解析为日期的最佳方法是什么，这样我就可以在查询中做这样的事情......

select * from table where formatted(date_created) > formatted(date_created) - 90

（退回过去 90 天内的所有内容）

我发现了一些类似的例子，这些示例从表示秒或毫秒的日期整数转换而来，但没有一个列本质上是存储为整数的日期字符串。

感谢对实现这一目标的最佳方式的任何想法

Answer 1

我

想知道解析为日期的最佳方法是什么，这样我就可以在查询中做这样的事情......

您可以将"日期转换为数字"（例如。 2018 年 5 月 27 日20180527），使用以下方法：

1. 演员到瓦尔查尔
2. 使用适当格式parse_datetime
3. 投射至今（因为parse_datetime返回timestamp）

例：

presto> SELECT CAST(parse_datetime(CAST(20180527 AS varchar), 'yyyyMMdd') AS date);
   _col0
------------
 2018-05-27

但是，这不一定是查询数据的最佳方式。通过使搜索条件适应数据的格式（反之亦然），您可以从谓词下推和分区修剪中受益。有关如何执行此操作的信息，请参阅@GordonLinoff答案。

Answer 2

您可以在整数或日期的世界中进行比较。 您不妨将当前日期减去 90 天转换为一个数字：

select t.*
from t
where date_created >= cast(date_format(current_date - interval '90 day',
                                       '%Y%m%d'
                                      ) as int
                          );

Answer 3

下面的查询对任何数据库都是索引友好的，因为它不使用索引列上的函数

select * from table where date_created > timestamp (formatted(date) - 90)

Answer 4

此外，假设我们有date格式20211011_1234并且我们想要早一个月的日期并希望恢复原始格式，我们可以使用以下格式将date转换为int，反之亦然。

select cast(date_format(
CAST(parse_datetime(cast(
split_part('20211011_1234', '_', 1) as varchar), 'yyyyMMdd') 
AS date) - interval '30' day ,'%Y%m%d') as int) as column_name