Styjun

Do Jedi mind tricks work on Ewoks?

What word describes the sound of an instrument based on the shape of the waveform of its sound?

Where did Lovecraft write about Carcosa?

Ab major 9th chord in Bach

Installing Debian 10, upgrade to stable later?

Hostile Divisor Numbers

What does the phrase "go for the pin" mean here?

Playing Doublets with the Primes

Why are condenser mics so much more expensive than dynamics?

Why increasing of the temperature of the objects like wood, paper etc. doesn't fire them?

What is monoid homomorphism exactly?

Is there precedent or are there procedures for a US president refusing to concede to an electoral defeat?

While drilling into kitchen wall, hit a wire - any advice?

Efficient deletion of specific list entries

Can an earth elemental drag a tiny creature underground with Earth Glide?

How to replace space with '+' symbol in a triangular array?

How to speed up large double sums in a table?

How did the Apollo guidance computer handle parity bit errors?

What is the thing used to help pouring liquids called?

What does the copyright in a dissertation protect exactly?

Convert Numbers To Emoji Math

Is crescere the correct word meaning to to grow or cultivate?

Referring to person by surname, keep or omit "von"?

What is a common way to tell if an academic is "above average," or outstanding in their field? Is their h-index (Hirsh index) one of them?

Converting '03/11/2019 11:45:00' to hive timestamp

Convert string to timestamp hiveConversion of String to Date/Timestamp in Hiveconverting date to timestamp in hiveTIMESTAMP in hive?Convert date '11/2/2014' (mm/d/yyyy) into timestamp in hiveConvert String to Timestamp in Hive HQLHow to convert date in YYYYMMDD in Hive to unix timestampConvert a String value in dataframe to timestamp and Store in HiveConvert Hive column from String to timestamp having NaT valuesHow to convert timestamp from different timezones in Hive

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

2

I have dates in the format '3/20/2019 17:23:00' or '2/1/2019 11:44:00' and they come in as null when I specify the type as timestamp in hive. I am trying to convert it to with the following code but I'm not sure if it's not possible or if I'm specifying the pattern incorrectly.

For example one of these date variables is called 'on_dtml'

from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'))

I wrote the values as string format in one table, defined a second table where they are in timestamp, and now I am trying to write the values from the table where they are stored as string to the second table with the code above. Please help

insert into table test_table 
select variable1 string, from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'));

hive

share|improve this question

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

add a comment | 

2

I have dates in the format '3/20/2019 17:23:00' or '2/1/2019 11:44:00' and they come in as null when I specify the type as timestamp in hive. I am trying to convert it to with the following code but I'm not sure if it's not possible or if I'm specifying the pattern incorrectly.

For example one of these date variables is called 'on_dtml'

from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'))

I wrote the values as string format in one table, defined a second table where they are in timestamp, and now I am trying to write the values from the table where they are stored as string to the second table with the code above. Please help

insert into table test_table 
select variable1 string, from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'));

hive

share|improve this question

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

add a comment | 

I have dates in the format '3/20/2019 17:23:00' or '2/1/2019 11:44:00' and they come in as null when I specify the type as timestamp in hive. I am trying to convert it to with the following code but I'm not sure if it's not possible or if I'm specifying the pattern incorrectly.

For example one of these date variables is called 'on_dtml'

from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'))

I wrote the values as string format in one table, defined a second table where they are in timestamp, and now I am trying to write the values from the table where they are stored as string to the second table with the code above. Please help

insert into table test_table 
select variable1 string, from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'));

hive

share|improve this question

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'))

insert into table test_table 
select variable1 string, from_unixtime(unix_timestamp(on_dtml, 'MM/DD/YY HH:MM:SS'));

share|improve this question

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

share|improve this question

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

edited Mar 23 at 5:40

leftjoin

10.9k32356

edited Mar 23 at 5:40

leftjoin

10.9k32356

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

asked Mar 23 at 4:40

Harry BarsegyanHarry Barsegyan

164

1 Answer
1

active

oldest

votes

0

Correct pattern is 'MM/dd/yyyy HH:mm:ss':

 from_unixtime(unix_timestamp(on_dtml, 'MM/dd/yyyy HH:mm:ss'));

Read about format patterns here: SimpleDateFormat Upper/lower case matters a lot in the pattern.

See test here: http://demo.gethue.com/hue/editor?editor=292420

select from_unixtime(unix_timestamp('3/20/2019 17:23:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-03-20 17:23:00

One more test:

select from_unixtime(unix_timestamp('2/1/2019 11:44:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-02-01 11:44:00

share|improve this answer

edited Mar 23 at 7:05

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

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

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55310664%2fconverting-03-11-2019-114500-to-hive-timestamp%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

0

Correct pattern is 'MM/dd/yyyy HH:mm:ss':

 from_unixtime(unix_timestamp(on_dtml, 'MM/dd/yyyy HH:mm:ss'));

Read about format patterns here: SimpleDateFormat Upper/lower case matters a lot in the pattern.

See test here: http://demo.gethue.com/hue/editor?editor=292420

select from_unixtime(unix_timestamp('3/20/2019 17:23:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-03-20 17:23:00

One more test:

select from_unixtime(unix_timestamp('2/1/2019 11:44:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-02-01 11:44:00

share|improve this answer

edited Mar 23 at 7:05

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

add a comment | 

0

Correct pattern is 'MM/dd/yyyy HH:mm:ss':

 from_unixtime(unix_timestamp(on_dtml, 'MM/dd/yyyy HH:mm:ss'));

Read about format patterns here: SimpleDateFormat Upper/lower case matters a lot in the pattern.

See test here: http://demo.gethue.com/hue/editor?editor=292420

select from_unixtime(unix_timestamp('3/20/2019 17:23:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-03-20 17:23:00

One more test:

select from_unixtime(unix_timestamp('2/1/2019 11:44:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-02-01 11:44:00

share|improve this answer

edited Mar 23 at 7:05

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

add a comment | 

Correct pattern is 'MM/dd/yyyy HH:mm:ss':

 from_unixtime(unix_timestamp(on_dtml, 'MM/dd/yyyy HH:mm:ss'));

Read about format patterns here: SimpleDateFormat Upper/lower case matters a lot in the pattern.

See test here: http://demo.gethue.com/hue/editor?editor=292420

select from_unixtime(unix_timestamp('3/20/2019 17:23:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-03-20 17:23:00

One more test:

select from_unixtime(unix_timestamp('2/1/2019 11:44:00', 'MM/dd/yyyy HH:mm:ss'));

Result:

2019-02-01 11:44:00

share|improve this answer

edited Mar 23 at 7:05

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

 from_unixtime(unix_timestamp(on_dtml, 'MM/dd/yyyy HH:mm:ss'));

select from_unixtime(unix_timestamp('3/20/2019 17:23:00', 'MM/dd/yyyy HH:mm:ss'));

2019-03-20 17:23:00

select from_unixtime(unix_timestamp('2/1/2019 11:44:00', 'MM/dd/yyyy HH:mm:ss'));

2019-02-01 11:44:00

share|improve this answer

edited Mar 23 at 7:05

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356

answered Mar 23 at 5:49

leftjoinleftjoin

10.9k32356