SQL 雅典娜大于日期列中的条件

Athena greater than condition in date column

提问by efbbrown

I have the following query that I am trying to run on Athena.

我尝试在 Athena 上运行以下查询。

SELECT observation_date, COUNT(*) AS count
FROM db.table_name
WHERE observation_date > '2017-12-31'
GROUP BY observation_date

However it is producing this error:

但是它产生了这个错误：

SYNTAX_ERROR: line 3:24: '>' cannot be applied to date, varchar(10)

This seems odd to me. Is there an error in my query or is Athena not able to handle greater than operators on date columns?

这对我来说似乎很奇怪。我的查询中是否存在错误，或者 Athena 是否无法处理日期列上的大于运算符？

Thanks!

谢谢！

回答by Barry Piccinni

You need to use a cast to format the date correctly before making this comparison. Try the following:

在进行此比较之前，您需要使用强制转换来正确设置日期格式。请尝试以下操作：

SELECT observation_date, COUNT(*) AS count
FROM db.table_name
WHERE observation_date > CAST('2017-12-31' AS DATE)
GROUP BY observation_date

Check it out in Fiddler: SQL Fidle

在 Fiddler 中查看：SQL Fiddle

UPDATE 17/07/2019

更新 17/07/2019

In order to reflect comments

为了反映评论

SELECT observation_date, COUNT(*) AS count
FROM db.table_name
WHERE observation_date > DATE('2017-12-31')
GROUP BY observation_date

回答by Zerodf

You can also use the datefunction which is a convenient alias for CAST(x AS date):

您还可以使用date函数，这是一个方便的别名CAST(x AS date)：

SELECT * 
FROM date_data
WHERE trading_date >= DATE('2018-07-06');