store SET time_zone as part of a VIEW statement
Posted by: kavasmlikon kavasmlikon
Date: April 05, 2012 06:06PM

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;

Options: ReplyQuote


Subject
Written By
Posted
store SET time_zone as part of a VIEW statement
April 05, 2012 06:06PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.