DATE_FORMAT(CONVERT_TZ) coming up empty in MySQL

Question

Can find many instances of date_format OR convert_tz questions but none with both, so sorry if this has been asked and answered.

Anyway... I'm coming up empty with the following query.

"SELECT... MAX(DATE_FORMAT(CONVERT_TZ(p.posted_on, 'UTC', 'America/New_York'), 
'%e-%b-%y %l:%i %p')) AS last, MIN(DATE_FORMAT(CONVERT_TZ(p.posted_on, 'UTC', 
 'America/New_York'), '%e-%b-%y %l:%i %p')) AS first.....";

Array
(
    [thread_id] => 7
    [subject] => Sample Thread
    [username] => troutster
    [responses] => 1
    [last] => 
    [first] => 
)

I can get rid of the CONVERT_TZ and just use p.posted_on by itself and it works fine, so I'm assuming I'm doing something wrong in the conversion. Thanks for any help you can provide.

Answer 1

There is no problem in query.it is problem with the data. If you want to use timezone with name,first you have to load timezone.

You can check timezone related tables which will be available in 'mysql' database

Answer 2

It looks like you haven't installed timezone tables so it will not recognize your timezone. A simple trial of CONVERT_TZ may prove this.

you will need to download it from https://dev.mysql.com/downloads/timezones.html

Please see this for reference: convert_tz returns null