Need help with an epoch to human date in a query

[elentz]

I have some log entries that have the date / time in epoch time. It is in a MySql db and I have a query to show everything I want. The problem is how do I change the epoch time to human time / date when the query is run? The query I am using is:

$sql = "Select stime,direction,Trunk,Source,destination,CallLength from export where Trunk = 'nexVortex'";

The field I need to change is stime Can someone show me how to do this? It was suggested to use an alias for this but I am at a loss as to how to do it.
Thanks for any help.

 

[vacunita]

Technically you shouldn't have to. Mysql automatically returns the timestamp in a human readable format as: YYYY-MM-DD HH:MM:SS

If you are using a custom timestamp as an integer column then you can use the from_unixtime function to convert it to a readable format.

SELECT from_unixtime(stime) as formattedTimestamp, direction, ...

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_from-unixtime

With that said, this was a MYSQL question, and not a PHP related question. as such the better forum for it would have been forum436

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[elentz]

Thanks for the reply. Actually I JUST figured it out and you are right I went to the wrong forum. But haer is what I used and it works as I need it

$sql = "Select FROM_UNIXTIME(stime,'%h:%i:%s %D %M %x'),direction,Trunk,Source,destination,CallLength from export where Trunk = 'nexVortex'";


Thanks again

 

[vacunita]

Glad you solved it. Just as a note if you don't give your formatting an alias it will show up as the command.

So its column title will be: FROM_UNIXTIME(stime,'%h:%i:%s %D %M %x'), all that.

If you are using php to read the rows by using the numbered index of the column then this won't be an issue.

----------------------------------
Phil AKA Vacunita
----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

Web & Tech

 

[elentz]

I see what you mean. I guess since my result is being passed to BaaGrid and I am setting the column names there it all works out.

Lucky I guess

Thanks