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!

SET time_zone = '+10:00';
SELECT AS edge_user_id, eu.firstname, eu.lastname, eu.occupation, eu.statusmessage, 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, AS checkin_sublocation FROM ((((identification_media im JOIN identification_media_type imt ON im.type = JOIN people ON people.identification_id = JOIN edge_users eu ON = people.edge_users_id) JOIN check_in ON check_in.identification_media_id = LEFT JOIN locations ON check_in.sublocation = ORDER BY checkin_timestamp DESC;

Options: ReplyQuote

Written By
Re: store SET time_zone as part of a VIEW statement
April 10, 2012 02:33AM

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.