MySQL Forums :: Database Design and Data Modelling :: store SET time_zone as part of a VIEW statement

Hi,

Is there a way I can store "SET time_zone" and a subsequent mysql query as part of the same VIEW. i.d. whenever the view is called, it inherently sets the time_zone first?

My context:
I have a MySQL DB with a table that stores local Brisbane/Australia timestamps. I want to create a view that also calculates the time passed since the timestamp and returns it as part of a column in the view. As my server is in the US, the calculated time difference is wrong. Therefore I'd like to set the time_zone to Brisbane time (SET time_zone = '+10:00';), before the actual SQL statement.
If I paste below's two sql statements in phpmyadmin, it spits out the right timedifference. however, if I want to CREATE a view from this result in phpmyadmin, it creates it but seems to ignore the time_zone command. thanks!


SET time_zone = '+10:00';
SELECT eu.id AS edge_user_id, eu.firstname, eu.lastname, eu.occupation, eu.statusmessage, imt.name AS imt_name, im.ThirdPartyID AS im_id, check_in.check_in_time AS checkin_timestamp, (SELECT TIMESTAMPDIFF(MONTH, check_in.check_in_time, (SELECT NOW()))) AS months_since_checkin, (SELECT TIMESTAMPDIFF(DAY, check_in.check_in_time, (SELECT NOW()))) AS days_since_checkin, (SELECT TIMESTAMPDIFF(HOUR, check_in.check_in_time, (SELECT NOW()))) AS hours_since_checkin, (SELECT TIMESTAMPDIFF(MINUTE, check_in.check_in_time, (SELECT NOW()))) AS minutes_since_checkin, locations.name AS checkin_sublocation FROM ((((identification_media im JOIN identification_media_type imt ON im.type = imt.id) JOIN people ON people.identification_id = im.id) JOIN edge_users eu ON eu.id = people.edge_users_id) JOIN check_in ON check_in.identification_media_id = im.id) LEFT JOIN locations ON check_in.sublocation = locations.id ORDER BY checkin_timestamp DESC;