Re: store SET time_zone as part of a VIEW statement

[kavasmlikon kavasmlikon]

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;