英文:
PRESTO: Calculating Date Difference Excluding Days: Precision in Months and Handling Greater Than 1 Month Condition
问题
问题：我在尝试计算两个日期之间的月份差异时遇到了问题，不包括天数。以下是情景：
WITH dates AS (
SELECT CAST('2000-03-01 00:00:00' AS TIMESTAMP) AS start_date,
CAST('2000-04-02 00:00:00' AS TIMESTAMP) AS end_date
),
calculated_age AS (
SELECT DATE_DIFF('month', start_date, end_date) AS month_diff
FROM dates
)
SELECT *
FROM calculated_age
WHERE month_diff > 1;
在上述情景中，实际日期差异超过了1个月和1天。然而，在Presto中，DATE_DIFF函数仅计算整数月份的差异，忽略了天数。因此，它认为差异不大于1个月。
我想知道如何处理这种需要精确计算月份差异并执行“大于1个月”条件的情况。如果DATE_DIFF不适用，有什么替代查询或计算可以解决这个问题？
英文:
Question: I'm facing an issue while trying to calculate the difference between two dates in months, excluding the days. Here's the scenario:
WITH dates AS (
SELECT CAST('2000-03-01 00:00:00' AS TIMESTAMP) AS start_date,
CAST('2000-04-02 00:00:00' AS TIMESTAMP) AS end_date
),
calculated_age AS (
SELECT DATE_DIFF('month', start_date, end_date) AS month_diff
FROM dates
)
SELECT *
FROM calculated_age
WHERE month_diff > 1;
In the above scenario, the actual date difference is more than 1 month and 1 day. However, the DATE_DIFF function in Presto calculates the difference only in whole months, discarding the days. As a result, it doesn't consider the difference to be greater than 1 month.
I would like to know how I can handle such scenarios where I need to calculate the date difference in months with precision and perform a condition like "greater than 1 month" accurately. If DATE_DIFF is not suitable, what alternative query or calculation can be used to address this issue?
答案1
得分: 2
你可以将其改写成一个查询：“结束日期是否大于开始日期 + 1个月？” 使用 date_add 函数。
WITH dates (start_date, end_date) AS (
VALUES (TIMESTAMP '2000-03-01 00:00:00', TIMESTAMP '2000-04-02 00:00:00')
)
SELECT *
WHERE end_date > date_add('month', 1, start_date)
FROM dates
英文:
You could turn it around and formulate a query for "is the end date larger than the start date + 1 month?" using the date_add function
WITH dates (start_date, end_date) AS (
VALUES (TIMESTAMP '2000-03-01 00:00:00', TIMESTAMP '2000-04-02 00:00:00')
)
SELECT *
WHERE end_date > date_add('month', 1, start_date)
FROM dates
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论