2011-09-02 01:13
浏览 127

Mysql Unix时间戳存储?

I got a bit of a problem here. My database stores unix timestamps as a string 2011-09-01 20:22:36 and I need it as a Unix Timestamp ########### so I can compare it using a > then symbol. But I also need to have it automatically set the timestamp on update (ON UPDATE CURRENT TIMESTAMP) as well as have a default of the timestamp which is not really that important cause I can do that in PHP if I need to.

How can I do this? timestamp is now a date/time combo string and not a integre so I cannot compare it?

My comparison string is

$sql = sprintf("SELECT nid, field_date_value, field_movie_location_value FROM content_type_mobile_event WHERE updated<'%s'", $vid);

Incase anyone is wondering.

图片转代码服务由CSDN问答提供 功能建议

我在这里遇到了一些问题。 我的数据库将unix时间戳存储为字符串 2011-09-01 20:22:36 ,我需要它作为Unix时间戳###########所以我可以使用它来比较它 a&gt; 然后符号。 但我还需要让它自动设置更新时的时间戳( ON UPDATE CURRENT TIMESTAMP )以及时间戳的默认值 这不是真的那样 重要的原因如果需要,我可以在PHP中这样做。

我该怎么做? timestamp 现在是一个日期/时间组合字符串,而不是一个整数,所以我无法比较它?

我的比较字符串是 < p> $ sql = sprintf(“SELECT nid,field_date_value,field_movie_location_value FROM content_type_mobile_event WHERE updated&lt;'%s'”,$ vid);

Incase any is is is 想知道。

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

2条回答 默认 最新

  • duanfei1987
    duanfei1987 2011-09-02 01:16

    Use the UNIX_TIMESTAMP() function to convert it inside your query. If you must compare it to a Unix timestamp from PHP, it is easiest to allow MySQL to handle the column's conversion on its end.

    $sql = sprintf("SELECT nid, othercols FROM content_type_mobile_event WHERE UNIX_TIMESTAMP(updated) < '%s'", $vid);
    点赞 评论
  • duangua5308
    duangua5308 2011-09-02 01:15

    You can compare DATETIME columns with operators like > or <, so I don't see what the problem is. For example, you can do this :

    SELECT *
      FROM table
     WHERE your_column > NOW() - INTERVAL 2 HOUR;

    If you really need unix timestamps (you shouldn't, it's a bad habit PHP users have), you can use the function UNIX_TIMESTAMP :

      FROM table;

    You can also use FROM_UNIXTIME to convert a unix timestamp to a valid date :

    SELECT *
      FROM table
     WHERE your_column > FROM_UNIXTIME($data)

    This is what I would use if I really had to use a unix timestamp, but most of the time, you can do without.

    点赞 评论