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 (
# Insert a value to test…
insert into tmp.test_dt
(d, t) values
(‘2011-01-01′, ’11:59:59 PM’)
# Run the conversion sql…
unix_timestamp(concat(cast(d as char), ‘ ‘, cast(time_format(str_to_date(t,’%r’),’%T’) as char) ) )
# Output: 1293926399
# To test
# 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.