How to get DATE_DIFF in decimal
0
My friends are migrating from Netezza to BigQuery. In Netezza "month_between" function gives them back a decimal result. Meanwhile in BQ date_diff is always an integer. Is there a way to get fractional output in BQ?
(their logic)
sql
google-bigquery asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
add a comment |
0
My friends are migrating from Netezza to BigQuery. In Netezza "month_between" function gives them back a decimal result. Meanwhile in BQ date_diff is always an integer. Is there a way to get fractional output in BQ?
(their logic)
sql
google-bigquery asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10
add a comment |
0
0
0
My friends are migrating from Netezza to BigQuery. In Netezza "month_between" function gives them back a decimal result. Meanwhile in BQ date_diff is always an integer. Is there a way to get fractional output in BQ?
(their logic)
sql
google-bigquery asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
My friends are migrating from Netezza to BigQuery. In Netezza "month_between" function gives them back a decimal result. Meanwhile in BQ date_diff is always an integer. Is there a way to get fractional output in BQ?
(their logic)
sql
google-bigquery sql
google-bigquery asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
edited Nov 21 at 0:16
asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
asked Nov 21 at 0:00
Felipe Hoffa
20.8k249106
20.8k249106
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10
add a comment |
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10
add a comment |
1 Answer
1
active
oldest
votes
1
You could write an UDF:
CREATE TEMP FUNCTION months_between_impl(date_1 DATE, date_2 DATE) AS (
CASE
WHEN date_1 = date_2
THEN 0
WHEN EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL 1 DAY)) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(date_1,date_2, MONTH)
WHEN EXTRACT(DAY FROM date_1) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) + 1/31
ELSE DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) - 1 + EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL -1 DAY)) / 31 + (31 - EXTRACT(DAY FROM date_2) + 1) / 31
END
);
CREATE TEMP FUNCTION months_between(date_1 DATE, date_2 DATE) AS (
TRUNC(months_between_impl(date_1, date_2),9)
);
WITH
t AS (
SELECT DATE("2005-02-02") AS from_date, DATE("2005-01-01") AS to_date, "1.032258064516129" AS Expected
UNION ALL
SELECT DATE("2007-03-15"), DATE("2007-02-20"), "0.838709677419354"
UNION ALL
SELECT DATE("2008-03-29"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2008-03-31"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2005-11-29"), DATE("2006-03-01"), "-3.096774194"
UNION ALL
SELECT DATE("1993-07-01"), DATE("1993-03-31"), "3.03225806"
UNION ALL
SELECT DATE("2005-03-31"), DATE("2005-01-01"), "2.967741935"
UNION ALL
SELECT DATE("2008-03-30"), DATE("2008-02-29"), "1.032258064516129"
)
SELECT
from_date, to_date, expected, months_between(from_date, to_date) months_Between
FROM t;
added by Mikhail
Below is real run on Netezza showing that above UDF actually returns totally correct result (as for some reason the numbers in expected column are not what really Netezza returns - rather correct numbers are under result column - which as I mentioned exactly what Felipe's UDF produces)
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked asexpected- the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)
– Mikhail Berlyant
Nov 21 at 1:44
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403414%2fhow-to-get-date-diff-in-decimal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You could write an UDF:
CREATE TEMP FUNCTION months_between_impl(date_1 DATE, date_2 DATE) AS (
CASE
WHEN date_1 = date_2
THEN 0
WHEN EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL 1 DAY)) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(date_1,date_2, MONTH)
WHEN EXTRACT(DAY FROM date_1) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) + 1/31
ELSE DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) - 1 + EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL -1 DAY)) / 31 + (31 - EXTRACT(DAY FROM date_2) + 1) / 31
END
);
CREATE TEMP FUNCTION months_between(date_1 DATE, date_2 DATE) AS (
TRUNC(months_between_impl(date_1, date_2),9)
);
WITH
t AS (
SELECT DATE("2005-02-02") AS from_date, DATE("2005-01-01") AS to_date, "1.032258064516129" AS Expected
UNION ALL
SELECT DATE("2007-03-15"), DATE("2007-02-20"), "0.838709677419354"
UNION ALL
SELECT DATE("2008-03-29"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2008-03-31"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2005-11-29"), DATE("2006-03-01"), "-3.096774194"
UNION ALL
SELECT DATE("1993-07-01"), DATE("1993-03-31"), "3.03225806"
UNION ALL
SELECT DATE("2005-03-31"), DATE("2005-01-01"), "2.967741935"
UNION ALL
SELECT DATE("2008-03-30"), DATE("2008-02-29"), "1.032258064516129"
)
SELECT
from_date, to_date, expected, months_between(from_date, to_date) months_Between
FROM t;
added by Mikhail
Below is real run on Netezza showing that above UDF actually returns totally correct result (as for some reason the numbers in expected column are not what really Netezza returns - rather correct numbers are under result column - which as I mentioned exactly what Felipe's UDF produces)
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked asexpected- the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)
– Mikhail Berlyant
Nov 21 at 1:44
add a comment |
1
You could write an UDF:
CREATE TEMP FUNCTION months_between_impl(date_1 DATE, date_2 DATE) AS (
CASE
WHEN date_1 = date_2
THEN 0
WHEN EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL 1 DAY)) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(date_1,date_2, MONTH)
WHEN EXTRACT(DAY FROM date_1) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) + 1/31
ELSE DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) - 1 + EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL -1 DAY)) / 31 + (31 - EXTRACT(DAY FROM date_2) + 1) / 31
END
);
CREATE TEMP FUNCTION months_between(date_1 DATE, date_2 DATE) AS (
TRUNC(months_between_impl(date_1, date_2),9)
);
WITH
t AS (
SELECT DATE("2005-02-02") AS from_date, DATE("2005-01-01") AS to_date, "1.032258064516129" AS Expected
UNION ALL
SELECT DATE("2007-03-15"), DATE("2007-02-20"), "0.838709677419354"
UNION ALL
SELECT DATE("2008-03-29"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2008-03-31"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2005-11-29"), DATE("2006-03-01"), "-3.096774194"
UNION ALL
SELECT DATE("1993-07-01"), DATE("1993-03-31"), "3.03225806"
UNION ALL
SELECT DATE("2005-03-31"), DATE("2005-01-01"), "2.967741935"
UNION ALL
SELECT DATE("2008-03-30"), DATE("2008-02-29"), "1.032258064516129"
)
SELECT
from_date, to_date, expected, months_between(from_date, to_date) months_Between
FROM t;
added by Mikhail
Below is real run on Netezza showing that above UDF actually returns totally correct result (as for some reason the numbers in expected column are not what really Netezza returns - rather correct numbers are under result column - which as I mentioned exactly what Felipe's UDF produces)
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked asexpected- the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)
– Mikhail Berlyant
Nov 21 at 1:44
add a comment |
1
1
1
You could write an UDF:
CREATE TEMP FUNCTION months_between_impl(date_1 DATE, date_2 DATE) AS (
CASE
WHEN date_1 = date_2
THEN 0
WHEN EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL 1 DAY)) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(date_1,date_2, MONTH)
WHEN EXTRACT(DAY FROM date_1) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) + 1/31
ELSE DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) - 1 + EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL -1 DAY)) / 31 + (31 - EXTRACT(DAY FROM date_2) + 1) / 31
END
);
CREATE TEMP FUNCTION months_between(date_1 DATE, date_2 DATE) AS (
TRUNC(months_between_impl(date_1, date_2),9)
);
WITH
t AS (
SELECT DATE("2005-02-02") AS from_date, DATE("2005-01-01") AS to_date, "1.032258064516129" AS Expected
UNION ALL
SELECT DATE("2007-03-15"), DATE("2007-02-20"), "0.838709677419354"
UNION ALL
SELECT DATE("2008-03-29"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2008-03-31"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2005-11-29"), DATE("2006-03-01"), "-3.096774194"
UNION ALL
SELECT DATE("1993-07-01"), DATE("1993-03-31"), "3.03225806"
UNION ALL
SELECT DATE("2005-03-31"), DATE("2005-01-01"), "2.967741935"
UNION ALL
SELECT DATE("2008-03-30"), DATE("2008-02-29"), "1.032258064516129"
)
SELECT
from_date, to_date, expected, months_between(from_date, to_date) months_Between
FROM t;
added by Mikhail
Below is real run on Netezza showing that above UDF actually returns totally correct result (as for some reason the numbers in expected column are not what really Netezza returns - rather correct numbers are under result column - which as I mentioned exactly what Felipe's UDF produces)
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
You could write an UDF:
CREATE TEMP FUNCTION months_between_impl(date_1 DATE, date_2 DATE) AS (
CASE
WHEN date_1 = date_2
THEN 0
WHEN EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL 1 DAY)) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(date_1,date_2, MONTH)
WHEN EXTRACT(DAY FROM date_1) = 1
AND EXTRACT(DAY FROM DATE_ADD(date_2, INTERVAL 1 DAY)) = 1
THEN DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) + 1/31
ELSE DATE_DIFF(DATE_ADD(date_1, INTERVAL -1 DAY), date_2, MONTH) - 1 + EXTRACT(DAY FROM DATE_ADD(date_1, INTERVAL -1 DAY)) / 31 + (31 - EXTRACT(DAY FROM date_2) + 1) / 31
END
);
CREATE TEMP FUNCTION months_between(date_1 DATE, date_2 DATE) AS (
TRUNC(months_between_impl(date_1, date_2),9)
);
WITH
t AS (
SELECT DATE("2005-02-02") AS from_date, DATE("2005-01-01") AS to_date, "1.032258064516129" AS Expected
UNION ALL
SELECT DATE("2007-03-15"), DATE("2007-02-20"), "0.838709677419354"
UNION ALL
SELECT DATE("2008-03-29"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2008-03-31"), DATE("2008-02-29"), "1.0"
UNION ALL
SELECT DATE("2005-11-29"), DATE("2006-03-01"), "-3.096774194"
UNION ALL
SELECT DATE("1993-07-01"), DATE("1993-03-31"), "3.03225806"
UNION ALL
SELECT DATE("2005-03-31"), DATE("2005-01-01"), "2.967741935"
UNION ALL
SELECT DATE("2008-03-30"), DATE("2008-02-29"), "1.032258064516129"
)
SELECT
from_date, to_date, expected, months_between(from_date, to_date) months_Between
FROM t;
added by Mikhail
Below is real run on Netezza showing that above UDF actually returns totally correct result (as for some reason the numbers in expected column are not what really Netezza returns - rather correct numbers are under result column - which as I mentioned exactly what Felipe's UDF produces)
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
edited Nov 21 at 1:51
Mikhail Berlyant
55.2k43368
55.2k43368
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
answered Nov 21 at 0:09
Felipe Hoffa
20.8k249106
20.8k249106
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked asexpected- the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)
– Mikhail Berlyant
Nov 21 at 1:44
add a comment |
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked asexpected- the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)
– Mikhail Berlyant
Nov 21 at 1:44
1
1
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
Acknowledgments to my Google teammates that asked and solved this problem internally first.
– Felipe Hoffa
Nov 21 at 0:12
1
1
Btw, I just run your data on Netezza and my result shows slightly different result that is marked as
expected - the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)– Mikhail Berlyant
Nov 21 at 1:44
Btw, I just run your data on Netezza and my result shows slightly different result that is marked as
expected - the good news is that it is exactly same as output of your udf with the exception for row #5 - i will add image to your post - hope you will be ok with it :o)– Mikhail Berlyant
Nov 21 at 1:44
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53403414%2fhow-to-get-date-diff-in-decimal%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
isn't this just matter of translating integer number of days(or hours) into float number of month? just checking :o)
– Mikhail Berlyant
Nov 21 at 0:03
oops, I do have a working answer :)
– Felipe Hoffa
Nov 21 at 0:10