英文:
How to get year from number of day's in SparkSQL
问题
select
datediff('year', CURRENT_DATE, birthday) as user_age
from user_table limit 20
我尝试使用上面的代码，但它给我返回了下面的错误。它只接受两个日期，而我不想除以365。有没有其他函数可以使用。
英文:
select
datediff('year',CURRENT_DATE,birthday) as user_age
from user_table limit 20
I tried using the code above, but it gives me the error below. That it only takes two dates, and I don't want to divide by 365. Is there another function I can use.
答案1
得分: 1
You can use date_part to get the years of the two dates and then subtract them:
select date_part('year', CURRENT_DATE) - date_part('year', birthday) from user_table
This approach is prone to off-by-one errors.
To get around this error, you also have to take the day and month into account:
select
birthday,
date_part('year', CURRENT_DATE) - date_part('year', birthday) +
if((date_part('month', CURRENT_DATE) - date_part('month', birthday)) * 100 +
date_part('day', CURRENT_DATE) - date_part('day', birthday) < 0, -1, 0)
as age
from user_table
英文:
You can use date_part to get the years of the two dates and then substract them:
select date_part('year', CURRENT_DATE) - date_part('year', birthday) from user_table
This approach is prone to off-by-one errors.
To get around of this error, you also have to take the day and month into account:
select
birthday,
date_part('year', CURRENT_DATE) - date_part('year', birthday) +
if((date_part('month', CURRENT_DATE) - date_part('month', birthday)) * 100 +
date_part('day', CURRENT_DATE) - date_part('day', birthday) < 0, -1, 0)
as age
from user_table
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论