Need to subtract some hours from given timestamp in hive
Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')
Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37
I know that there is date_sub function in hive but it is only used to subtract days from the given timestamp. But I need to know if there is a way by which I can subtract hours or minutes or seconds.
I have also tried something like below as EDT timezone is 4 hours behind UTC (but getting wrong output):
SELECT to_date(from_UTC_timestamp(unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')*1000, 'EST6EDT')) as earliest_date; -- OUTPUT: 2017-12-31 (wrong)
So can anyone help me out with this?
mysql sql hive hiveql sql-date-functions
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
add a comment |
Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')
Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37
I know that there is date_sub function in hive but it is only used to subtract days from the given timestamp. But I need to know if there is a way by which I can subtract hours or minutes or seconds.
I have also tried something like below as EDT timezone is 4 hours behind UTC (but getting wrong output):
SELECT to_date(from_UTC_timestamp(unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')*1000, 'EST6EDT')) as earliest_date; -- OUTPUT: 2017-12-31 (wrong)
So can anyone help me out with this?
mysql sql hive hiveql sql-date-functions
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
add a comment |
Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')
Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37
I know that there is date_sub function in hive but it is only used to subtract days from the given timestamp. But I need to know if there is a way by which I can subtract hours or minutes or seconds.
I have also tried something like below as EDT timezone is 4 hours behind UTC (but getting wrong output):
SELECT to_date(from_UTC_timestamp(unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')*1000, 'EST6EDT')) as earliest_date; -- OUTPUT: 2017-12-31 (wrong)
So can anyone help me out with this?
mysql sql hive hiveql sql-date-functions
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
Input: unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')
Expected output is 4 hours delay from above utc input time i.e 01/15/2018 11:26:37
I know that there is date_sub function in hive but it is only used to subtract days from the given timestamp. But I need to know if there is a way by which I can subtract hours or minutes or seconds.
I have also tried something like below as EDT timezone is 4 hours behind UTC (but getting wrong output):
SELECT to_date(from_UTC_timestamp(unix_timestamp('01/15/2018 15:26:37', 'mm/dd/YYYY hh:mm:ss')*1000, 'EST6EDT')) as earliest_date; -- OUTPUT: 2017-12-31 (wrong)
So can anyone help me out with this?
mysql sql hive hiveql sql-date-functions
mysql sql hive hiveql sql-date-functions
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
asked Nov 21 '18 at 8:03
Bhuvi007Bhuvi007
196
196
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
It works fine.
select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
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
});
}
});
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%2f53407584%2fneed-to-subtract-some-hours-from-given-timestamp-in-hive%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
It works fine.
select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
add a comment |
It works fine.
select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
add a comment |
It works fine.
select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
It works fine.
select from_unixtime(unix_timestamp('01/15/2018 15:26:37', 'MM/dd/yyyy HH:mm:ss')-4*3600, 'MM/dd/yyyy HH:mm:ss')
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
answered Nov 21 '18 at 8:51
StrongYoungStrongYoung
3571213
3571213
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
add a comment |
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
Great !! This works.
– Bhuvi007
Nov 21 '18 at 9:08
add a comment |
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.
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%2f53407584%2fneed-to-subtract-some-hours-from-given-timestamp-in-hive%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
