Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help with an epoch to human date in a query

Status
Not open for further replies.

elentz

Technical User
Jan 9, 2007
81
US
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.
 
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.

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


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
 
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top