CODEWITHSUNDEEP
mysql
Tom
Tom

Reputation: 33

display convert_tz results as desired time format

I'm using a session time in a query to convert the results of my query to the desired timezone on the fly:

CONVERT_TZ(FROM_UNIXTIME(cal_events.event_start), @@session.time_zone, '+11:00') as "Time"

This gives me output like this: 2017-10-07 13:00:00

But I'd like to try and get %r output - eg: 1:00:00 PM

Can't figure out how to do that in my statement - any suggestions?

Upvotes: 1

Views: 593

Answers (1)

aknosis
aknosis

Reputation: 4318

Utilize DATE_FORMAT - https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format

DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(cal_events.event_start), @@session.time_zone, '+11:00'), '%Y-%m-%d %l:%i:%s %p') as "Time"

The key is the formatting %Y-%m-%d %l:%i:%s %p, specifically %p gives you AM/PM and %l gives you the 12 hour time format

Upvotes: 1

Related Questions