SQL Query - format Date

[singabaloo]

I'm trying to run a MySQL query to list the members of a group and show the last activity date. The results gives me the date in a serial number like 1349148662

Here is my code. What do I need to add or change to format the query correctly ?


SELECT `user`.`username` , `user`.`usergroupid` , `user`.`lastactivity`
FROM `user`
WHERE (
`user`.`usergroupid` = "7"
)
ORDER BY `user`.`lastactivity` DESC
LIMIT 0 , 30

[nerbert]

What it's returning is the timestamp. You can use PHP to convert to the date format of your choice with date(format, timestamp)

http://www.w3schools.com/php/func_date_date.asp

I think there's a way to have MySQL do it but I forgot how it works

[singabaloo]

Thanks. I've found theMySql format but I can't seem to get it to display. I get no errors so I assume it's not massively wrong.


SELECT `user`.`username`, `user`.`usergroupid` , DATE_FORMAT(`user`.`lastactivity`, '%d/%m/%Y') as `date_string`
FROM `user`
WHERE (`user`.`usergroupid` ='7' OR `user`.`usergroupid` ='5')
ORDER BY `user`.`lastactivity` DESC


I want to try and keep it as MySQL code so I can add it to the queries.php and allow other admins to run the canned query.

--------------- Added 1349158474 at 1349158474 ---------------

OK, I got it working using the FROM_UNIXTIME function.

SELECT username, usergroupid , FROM_UNIXTIME(lastactivity) AS ladate
FROM user
WHERE (usergroupid ='7' OR usergroupid ='5')
ORDER BY ladate DESC

[nerbert]

I guess that isn't a timestamp, but is generated by the PHP time() function at the time of the update. What MySQL wants is a proper timestamp, which looks like this: 2008-11-11 12:45:34. I don't know how you would convert using MySQL

[singabaloo]

As above, using the FROM_UNIXTIME I was able to reformat the lastactivity time correctly.

Now to tackle getting a search from two separate tables working.