change time format inside of query

[mohammad6006]

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

$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/en/date-and-time-functions.html#function_from-unixtime

[mohammad6006]

I think you want to use the mysql function FROM_UNIXTIME(). Here's the documentation: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_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]

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]

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:
$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
");