Skip navigation links

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


Advanced Search

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 kavasmlikon kavasmlikon 04/05/2012 06:06PM
Re: store SET time_zone as part of a VIEW statement Rick James 04/07/2012 12:15PM
Re: store SET time_zone as part of a VIEW statement kavasmlikon kavasmlikon 04/10/2012 02:33AM
Re: store SET time_zone as part of a VIEW statement Peter Brawley 04/08/2012 01:18PM
Re: store SET time_zone as part of a VIEW statement kavasmlikon kavasmlikon 04/10/2012 02:37AM


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.