2011-02-24 12:51
浏览 272

将服务器MySQL TimeStamp转换为UTC

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dpa84373
    dpa84373 2011-02-24 12:59

    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.

    点赞 评论
  • duanchong3075
    duanchong3075 2011-02-24 12:56

    SELECT UNIX_TIMESTAMP('your_date') AS your_date; in a query and
    $date('whatever_format', $timestamp_from_mysql); in php

    点赞 评论
  • dongyue1988
    dongyue1988 2013-09-28 16:46

    Internally a [MySQL timestamp column][1] is stored as [UTC][2] but when selecting a date MySQL will automatically convert it to the current session timezone.

    When storing a date, MySQL will assume that the date is in the current session timezone and convert it to UTC for storage.

    To select a timestamp column in UTC format

    no matter what timezone the current MySQL session is in:

    CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` 
    FROM `table_name`

    You can also set the sever or global or current session timezone to UTC and then select the timestamp like so:

    SELECT `timestamp_field` FROM `table_name`

    I made a cheatsheet here: Should MySQL have its timezone set to UTC?

    点赞 评论