'How can I get local time zone time in MySQL View

I have to create a View in MySQL. But in database date_from column stores data in 'UTC'. In view that data have to show in local time zone.

create view test_vw as
select convert_tz(wws.date_from,'UTC',@@system_time_zone) my_date,
    wws1.propvalue plate,
        wws.propvalue VIN,
        wws2.propvalue certificate,
        concat('<a href="', wwl.filepath,'">', wws2.propvalue,'</a>') d_file
from wp_wfu_log wwl,
     wp_wfu_userdata wws, 
     wp_wfu_userdata wws1,
     wp_wfu_userdata wws2
where wws.uploadid = wwl.uploadid
    and wws1.uploadid = wwl.uploadid
    and wws2.uploadid = wwl.uploadid
    AND wws.propkey = 1
    AND wws1.propkey = 0
    AND wws2.propkey = 2
    AND upper(wwl.uploadid) not in (select upper(uploadid) from wp_wfu_log where action = 'delete')
ORDER BY my_date DESC;

Can not put 3rd parameter of CONVERT_TZ() function static. View data can be shown from different time zones.

Got error in @@session_time_zone

How can I achieve this?

mysqlsqldatabasewordpressphpmyadmin


Solution 1:[1]

On your second line you have 'UCT' instead of '+0:00'.

This is because of how the CONVERT_TZ function works in MYSQL.

Look at this link.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 samargy_

Related Questions