Hello, Rafal.
At first, sorry for my query, it probably should look like this: select convert_tz(cast(from_unixtime(( cast(t as binary) div 1000)) as datetime),'+00:00','-07:00') from tvar; Because you use milliseconds, not seconds. >....(cast(s2u.value)).... Second... I think you've forgot to insert 'as binary' after 'cast(s2u.value...'. May be that will do: select u.user_id, u.login_name, convert_tz(cast(from_unixtime(cast(s2u.value as binary) div 1000 ) as datetime),'+00:00', '-07:00') from users u, setting_2_user s2u where s2u.setting_id = 150 and u.user_id = s2u.user_id; Rafal Kedziorski <[EMAIL PROTECTED]> wrote: > Hi, > > If I do this, I get "You have an error in your SQL syntax. Check the manual > that corresponds to your MySQL server version for the right syntax to use > near '(cast(from_unixtime(cast(s2u.value)) as datetime), '+00:00', '-" > > select > u.user_id, > u.login_name, > convert_tz(cast(from_unixtime(cast(s2u.value)) as datetime),'+00:00', > '-07:00') > from > users u, > setting_2_user s2u > where > s2u.setting_id = 150 > and > u.user_id = s2u.user_id > > > Best Regards, > Rafal > > At 16:43 12.11.2004, you wrote: >>Hello. >> >>The first thing which comes to mind (I didn't dig really deep) looks like: >> >>select convert_tz(cast(from_unixtime(cast(t as binary)) as datetime),'+00:00', >>'-07:00') from tvar; >> >>mysql> show create table tvar; >>+-------+---------------------------------------------------------------------------------------------+ >>| Table | Create >>Table >>| >>+-------+---------------------------------------------------------------------------------------------+ >>| tvar | CREATE TABLE `tvar` ( >> `t` varchar(20) default NULL >> ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | >> +-------+------------------------------------- >> >>mysql> select * from tvar; >>+------------+ >>| t | >>+------------+ >>| 1100273311 | >>+------------+ >> >> >> >>Rafal Kedziorski <[EMAIL PROTECTED]> wrote: >> > hi, >> > >> > I store in a column (varchar(20)) milliseconds. How can I format the value >> > in a select statement to right timezone? >> > >> > >> > Regards, >> > Rafal >> > >> > >> >> >>-- >>For technical support contracts, goto https://order.mysql.com/?ref=ensita >>This email is sponsored by Ensita.NET http://www.ensita.net/ >> __ ___ ___ ____ __ >> / |/ /_ __/ __/ __ \/ / Gleb Paharenko >> / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] >>/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET >> <___/ www.mysql.com >> >> >> >> >>-- >>MySQL General Mailing List >>For list archives: http://lists.mysql.com/mysql >>To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ ____ __ / |/ /_ __/ __/ __ \/ / Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET <___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
