Merging two columns into a single column and formatting the content to form an accurate date-time format in Hive?

Question

these are the 2 columns(month,year). I want to create a single column out of them having an accurate date-time format('YYYY-MM-DD HH:MM:SS') and add as new column in the table.

 Month     year
 12/ 3   2013 at 8:40pm
 12/ 3   2013 at 8:39pm
 12/ 3   2013 at 8:39pm
 12/ 3   2013 at 8:38pm
 12/ 3   2013 at 8:37pm

What could be the best possible hive query for the same? I'm not able to form an accurate regex for the.

Answer 1

I'm going to assume that the 12 is month and that 3 is day since you didn't specify. Also, you said you want HH:MM:SS but there is no seconds in your example so I don't know how you're going to get them in there. I also changed 8:37pm to 8:37am in your example to try both cases.

Query:

  select concat_ws(' ', concat_ws('-', yr, month, day)
                      , concat_ws(':', hour, minutes)) date_time
  from (
    select yr
      , case when length(month) < 2 then concat('0', month) else month end as month
      , case when length(day) < 2 then concat('0', day) else day end as day
      , case when instr(minutes, 'pm') > 0 then cast(hour+12 as int)
             when instr(minutes, 'am') > 0 and length(hour) < 2 then concat('0', hour)
             else hour end as hour
      , substr(minutes, 1, 2) minutes
    from (
    select ltrim(split(Month, '\\/')[1]) day
      , split(Month, '\\/')[0] month
      , split(year, ' ')[0] yr
      , split(split(year, ' ')[2], '\\:')[0] hour
      , split(split(year, ' ')[2], '\\:')[1] minutes
    from test.sample_data ) x ) y

Output:

date_time

2013-12-03 20:40
2013-12-03 20:39
2013-12-03 20:39
2013-12-03 20:38
2013-12-03 08:37

Answer 2

yea worked fine, thank you very much @GoBrewers14. You saved my day!

a little mismatch, this is the correct one:

select concat_ws(' ', concat_ws('-', yr, month, day), concat_ws(':', hour, minutes)) date_time
  from (
    select yr
      , case when length(month) < 2 then concat('0', month) else month end as month
      , case when length(day) < 2 then concat('0', day) else day end as day
      , case when instr(minutes, 'pm') > 0 then cast(hour+12 as int)
             when instr(minutes, 'am') > 0 and length(hour) < 2 then concat('0', hour) else hour end as hour
      , substr(minutes, 1, 2) minutes
            from (
            select ltrim(split(month, '\\/')[1]) day
              , ltrim(split(month, '\\/')[0]) month
              , split(year, ' ')[1] yr
              , split(split(year, ' ')[3], '\\:')[0] hour
              , split(split(year, ' ')[3], '\\:')[1] minutes
            from db.table_name )
                 x ) y limit 5;

Answer 3

I assume 12 is month and 3 day.In sql server 2008 try to this

select convert(datetime,REPLACE(Month+'/'+year,'at',''))