Converting 12 hour time Unix timestamp

Many a times I need to transform time in AM/PM format to unix timestamp for better performance.  The external data coming in has date and time in two separate columns like '2011-01-01' as date and '11:59:59 PM' as time.   And some other time, I also need to convert timezones typically from a UTC to PST and to do that check this out.

For example, converting "2011-01-01 11:59:59 PM" into corresponding unix timestamp results in an integer 1293926399.

# Create a temp table...
create table tmp.test_dt (
  d date,
  t varchar(20)
)engine=InnoDB


# Insert a value to test...
insert into tmp.test_dt
(d, t) values
('2011-01-01', '11:59:59 PM')


# Run the conversion sql...
SELECT 
   unix_timestamp(concat(cast(d as char),  ' ',  cast(time_format(str_to_date(t,'%r'),'%T') as char)  ) )
FROM tmp.test_dt
# Output: 1293926399


# To test
SELECT from_unixtime(1293926399)
# Output: "2011-01-01 23:59:59"


In the conversion select statement, time_format converts the time (t column) from 11:59:59 PM to 23:59:59 of datetime type which is then cast as char to concatenate with date.  After concat the string looks like "2011-01-01 23:59:59" which becomes input to unix_timestamp.