将服务器MySQL TimeStamp转换为UTC

Question

I've been using the timeago plugin (http://timeago.yarp.com/), it has been working fine on my localhost, which has its MySQL storing its data in the UTC timestamp, which the plugin needs to work.

However, when uploading my project to the server, a different type of timestamp is appearing in it MySQL database. I'm getting a timestamp like this: "Thursday, February 24, 2011 4:29 PM" from the server, whereas I need something like this: "2008-07-17T09:24:17Z"

Any idea how to convert the timestamps using php?

Edit: The timestamps stored in the wrong format in the database are automatically generated by mysql.

Edit 2: It's a field of type "timestamp" and default set to "CURRENT_TIMESTAMP" when row is being inserted in db

Answer 1

You are getting a weird string for MySQL, are you sure that it is in a Datetime field?

You can get a UNIX timestamp (seconds since epoch) from MySQL with the following function, this format is widely accepted over multiple platforms:

SELECT UNIX_TIMESTAMP( table.datetime_field ) as datetime_field FROM table

Using some PHP Functions you can convert this to the format you desire:

echo date( 'c', $record[ 'datetime_field' ] );

I think this would be sufficient for your problem.