duanpiyao2734
2014-02-20 00:37
浏览 392
已采纳

mySQL将时间戳与当前日期进行比较

I have a field in my table called created_at that is a time stamp. I am trying to write a query where is will give me data where the date in time stamp is the current date.

$result = mysql_query("SELECT *FROM visitors WHERE DATE(FROM_UNIXTIME(created_at)) = 
CURDATE()") or die(mysql_error());

I looked up how to get date from time stamp that is why I am using DATE(FROM_UNIXTIME(created_at)). My query is coming back with nothing found, but I do have created_at fields in my db that are todays date. What am I doing wrong?

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

我的表中有一个名为created_at的字段,它是一个时间戳。 我正在尝试编写一个查询,其中将给出数据,其中时间戳中的日期是当前日期。

  $ result = mysql_query(“SELECT * FROM visitor WHERE DATE(FROM_UNIXTIME(created_at))= 
 {nCURDATE()”)或die(mysql_error()); 
 <  / code>  
 
 

我查找了如何从时间戳中获取日期,这就是我使用DATE(FROM_UNIXTIME(created_at))的原因。 我的查询返回时未找到任何内容,但是 我的db中的created_at字段是今天的日期。 我做错了什么?

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

1条回答 默认 最新

  • dss524049 2014-02-20 00:42
    已采纳

    The timestamp data type includes seconds so the times will never match. You need to trim the seconds from both the "created_at" timestamp and the current date/time (now()) before comparing so the you have a chance of matching just the date parts. For Example:

    select * from visitors where DATE_FORMAT(created_at,'%Y%c%d')=DATE_FORMAT(now(),'%Y%c%d')
    
    已采纳该答案
    打赏 评论

相关推荐 更多相似问题