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.

score 1 · Answer 1 · answered May 13 '17 at 06:32

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

answered May 13 '17 at 06:32

MohanaPriyan

218
3
9

Thank you. I'm a newb so I was confused on my code. Seemed like it was correct. Didn't realize about the tz tables. – Mike Karr May 13 '17 at 16:00

score 0 · Answer 2 · answered May 13 '17 at 07:34

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

DATE_FORMAT(CONVERT_TZ) coming up empty in MySQL

2 Answers2