change time format inside of query

[mohammad6006]

How can I change lastavtivity format and add to another table?

PHP Code:

$vbulletin->db->query_write("
                    INSERT INTO `". TABLE_PREFIX ."mba_hozor`
                    (userid, dateline, date)
                    SELECT userid, lastactivity , " .vbdate('Y-m-d', lastactivity)."
                    FROM " . TABLE_PREFIX . "user
                "); 


vbdate('Y-m-d', lastactivity) not work in query

[kh99]

I think you want to use the mysql function FROM_UNIXTIME(). Here's the documentation: http://dev.mysql.com/doc/refman/5.5/..._from-unixtime

[mohammad6006]

Originally Posted by kh99

I think you want to use the mysql function FROM_UNIXTIME(). Here's the documentation: http://dev.mysql.com/doc/refman/5.5/..._from-unixtime

vbdate('Y-m-d', lastactivity) work out of the query

for example if I fetch query and store in variable like: $visitor[lastactivity] and then
vbdate('Y-m-d', $visitor[lastactivity]) was working

my problem is that i can not use it in the query

[kh99]

Originally Posted by mohammad6006

vbdate('Y-m-d', lastactivity) work out of the query

for example if I fetch query and store in variable like: $visitor[lastactivity] and then
vbdate('Y-m-d', $visitor[lastactivity]) was working

my problem is that i can not use it in the query

You would either have to figure out how to use FROM_UNIXTIME() in the query, or else write a loop to read each row from the user table, use vbdate to convert, then do a separate INSERT for each.

I forgot that vbdate handles the timezone conversion based on a user's selecte timezone. Do you want the times to be converted for each user's timezone, or just for your own?

[mohammad6006]

Originally Posted by kh99

You would either have to figure out how to use FROM_UNIXTIME() in the query, or else write a loop to read each row from the user table, use vbdate to convert, then do a separate INSERT for each.

I forgot that vbdate handles the timezone conversion based on a user's selecte timezone. Do you want the times to be converted for each user's timezone, or just for your own?

that is not important

I created a table with 3 columns : userid, dateline, date

I could select userid and lastactivity from "user" table and insert to userid and dateline in this table

but i want to convert lastactivity to Y-m-d format and insert into date column too

[kh99]

OK, then how about this:

PHP Code:

$vbulletin->db->query_write(" 
                    INSERT INTO `". TABLE_PREFIX ."mba_hozor` 
                    (userid, dateline, date) 
                    SELECT userid, lastactivity, FROM_UNIXTIME(lastactivity, '%Y-%m-%d') 
                    FROM " . TABLE_PREFIX . "user 
                ");