[SOLVED] MySQL getting time in specific offset

MySQL getting time in specific offset

SELECT (from_time user_offset) as start FROM `availabilities`;

I am trying to add current logged in user's timezone offset to the time column values I am fetching.

The value of from_time will be like 02:30:00 and value of offset will like +02:00

Does anybody know what would be appropriate approach for the same.

UPDATE:

I tried the following way:

SELECT id, TIME_FORMAT( (
TIME_FORMAT( from_time, '%H:%i' ) + '05:30' ) , '%H:%i'
) AS
START 
FROM `availabilities`;

I got 00:00, but the value should have been 02:00, as the value of from_time is 20:30

I even tried

SELECT 
id,
CONVERT_TZ(
    from_time, 
    '+00:00',
    '+05:30'
) AS `start`
FROM availabilities

But it works only if **from_time field has both date and time, for time it returns null**

Solution

Converting timezones its not just adding +/-X hours. It certainly should be more complex thing if you wants proper results.

I believe somthing like this may help

SELECT 
id,
SUBSTRING(CONVERT_TZ(
    CONCAT('2000-01-01 ', from_time), 
    time_format(TIMEDIFF(NOW(), UTC_TIMESTAMP), '+%H:%i'),
    user_offset
), 12, 5) AS `start`
FROM availabilities