select convert_tz(now(),@@session.time_zone,'+05:30')
replace '+05:30' with desired timezone. see here - https://stackoverflow.com/a/3984412/2359994
to format into desired time format, eg:
select DATE_FORMAT(convert_tz(now(),@@session.time_zone,'+05:30') ,'%b %d %Y %h:%i:%s %p')
you will get similar to this -> Dec 17 2014 10:39:56 AM