Just to answer my own question, in case anyone else googles for this...

FirstIy, don't know why timezone isn't displaying in my query results using a SQL Warehouse, but it works ok using a compute cluster, so I'm guessing it is a display issue rather than a data issue.

Secondly, from what I can tell, using the TO_TIMESTAMP function will apply the timezone for your session (which might not be the timezone you want). I didn't realise this could be solved pretty easily by simply pasting your timezone to the end of the date string, like this;

TO_TIMESTAMP(MyDateTimeString || ' Timezone', 'M/d/y h:m:s a z)

For example;

TO_TIMESTAMP(MyDateTimeString || ' America/Los_Angeles', 'M/d/y h:m:s a z)