Formatting unix timestamp for outputting

[Fou-Lu]

How do I go abouts formatting a timestamp while running the query on the db? As in, I want to list all members that have been online (vBulletin) from midnight to current time. I want to run a query like:

$query=$DB_site->query("SELECT * FROM user WHERE lastactivity=time()")

But I want to format the time and last activity to Ymd(time()) and Ymd(lastactivity). Unfortunatly, this doesn't work. Does anyone have any suggestions? Any help would be appreciated.

[firepages]

<?
$query=$DB_site->query("SELECT *,FROM_UNIXTIME(lastactivity,'%Y %D %M') as pretty_time,FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M') as now_time FROM user WHERE lastactivity=time()");
?>

not sure if you can do this all in one query but that may work?

[Fou-Lu]

Hmm, I have never thought of something like that before... let us see...

Nope, that didn't work. Any other suggestions? Also, this didn't produce an error, so it does work, just not the way I want it to.

[downsize]

maybe you have to format the times first? using strtotime() and date() within the query.


$query=$DB_site->query("SELECT * FROM user WHERE lastactivity>=".strtotime(date("Ymd"))."")


that should display anything from midnight current day and on

is that what you are looking to do?

[Fou-Lu]

LOL
Thanx for the help! I ended up just pulling all of the info, and sorting it after that. Originally, I didn't want to do that, because I used a template that had striped the , in the last user, but I ended up just using a substr instead. Works great now though! Thanx for the help!