dongmei8511
dongmei8511
2017-12-31 11:35

如何从sql中的特定日期获取数据

已采纳

I am trying to get the data of the current date in MySQL. I have a column named created_at which stores the date and time (2017-12-31 11:32:54)using the NOW() function using the statements,

    $stmt = $this->conn->prepare("INSERT INTO log(id, name, created_at) VALUES(?, ?, NOW())");
    $stmt->bind_param("ss", $id, $name);
    $result = $stmt->execute();

Now I want to ignore the time in the created_at column and retrieve the data of the current date (today's date).

I tried using this query,

SELECT * FROM log WHERE created_at = DATE_SUB(CURDATE(), INTERVAL 0 DAY)

But this results in zero rows selected.

Please help me solve this issue.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • dongyi2993 dongyi2993 4年前

    One option would be to wrap created_at with DATE(), and then compare to CURDATE():

    SELECT *
    FROM log
    WHERE DATE(created_at) = CURDATE();
    

    But this has the drawback of precluding the possibility of using an index on the created_at column. We could also phrase this as follows:

    SELECT *
    FROM log
    WHERE created_at >= CURDATE() AND created_at < CURDATE() + INTERVAL 1 DAY;
    

    This would allow an index to be used on created_at, though it is a bit more verbose.

    点赞 评论 复制链接分享
  • dpkpaxhzffixp8426 dpkpaxhzffixp8426 4年前

    this will also work

    SELECT *
    FROM log
    WHERE  DATE(created_at, '%Y-%m-%d')  = CURDATE();
    
    点赞 评论 复制链接分享