Skip navigation links

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


Advanced Search

Re: store SET time_zone as part of a VIEW statement
Posted by: kavasmlikon kavasmlikon ()
Date: April 10, 2012 02:33AM

thanks rick and peter! I have considered using TIMESTAMP for a long time, but decided to go with DATETIME as I make extensive use of the date() functions in MySQL. Anyway, all dates in my DB are now saved in DATETIME. This is fine as I only use my application in one timezone anyway (UTC +10:00). The only issue with that is that the actual server is in the US. As Peter indicated, the workaround with

"SET time_zone = '+10:00';"

works great for regular sql statements (example below). However, my problem is that I want want to create a VIEW that spits out the DATETIME in my local time_zone. That essentially means that my VIEW declaration needs to embed two statements, i.e. the "SET time_zone" statement as well as the actual SELECT statement. Can I mash them up in one and the same VIEW declaration? if yes, how?

thanks a lot!
mark


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.