Re: Milliseconds to date string

[Rafal Kedziorski]

Hi,
I get this:
"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 binary) div 1000 ) as dat"

I think, the problem is here:
select
    s2u.value as binary
from
    users u,
    setting_2_user s2u
where
    s2u.setting_id = 150
and
    u.user_id = s2u.user_id;
"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 'binary from 
users u, setting_2_user s2u where s2u.setting_id ="

I'm using MySQL 4.0.18.
Regards,
Rafal
At 18:50 12.11.2004, you wrote:
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]

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]