doufuhuang6981
doufuhuang6981
2015-07-09 11:05
浏览 410
已采纳

MySQL将到期时间戳与当前时间NOW()进行比较

I want to check my expiry date with current time using NOW() function

Process: I am adding reminder date time with jQuery UI Date time picker and its giving me time stamp like this 2015-07-9 14:36 it does not have any seconds but my created_at contain complete Unix time stamp.

But using the below command doesn't give me any result. MySQL date time set with PC date and time.

MySql Code:

SELECT * FROM `tbl_web_contact_signups` WHERE `expiry` = NOW()

Database:

+----+-----------------+---------------------+
| ID |      expiry     |     created_at      |
+----+-----------------+---------------------+
|  1 | 2015-07-9 14:36 | 2015-07-01 11:22:24 |
+----+-----------------+---------------------+
|  2 | 2015-07-9 14:38 | 2015-07-01 11:22:24 |
+----+-----------------+---------------------+
|  3 | 2015-07-9 14:40 | 2015-07-01 11:22:24 |
+----+-----------------+---------------------+
|  4 | 2015-07-9 14:50 | 2015-07-01 11:22:24 |
+----+-----------------+---------------------+

Expiry column type varchar

PS: i am creating reminders and want to check how many reminders i have today or this time.

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

2条回答 默认 最新

  • dream518518518
    dream518518518 2015-07-09 11:11
    已采纳

    now() means current datetime, do you really have entry for current datetime, please check..

    For check remonders of today use this query:

    SELECT * FROM `tbl_web_contact_signups` WHERE date(`expiry`) = CURDATE();
    

    For check reminders of today and current hour use this query:

    SELECT * FROM `tbl_web_contact_signups` WHERE date(`expiry`) = CURDATE() and hour(`expiry`) = hour(now());
    

    For check reminders of today and current hour and current minute use this query:

    SELECT * FROM `tbl_web_contact_signups` WHERE date(`expiry`) = CURDATE() and hour(`expiry`) = hour(now()) and Minute(`expiry`)=minute(now());
    
    点赞 评论
  • douhang5460
    douhang5460 2015-07-09 11:28

    Try this

     SELECT * FROM tbl_web_contact_signups WHERE  expiry = DATE_FORMAT(NOW(), '%d-%m-%Y %H:%m') ;
    

    You must have record in expiry column with that particular time(minute) of query firing.

    or just check records for today with

    SELECT * FROM tbl_web_contact_signups WHERE  DATE(expiry) = CURDATE();
    
    点赞 评论

相关推荐