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

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.

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

我想使用NOW()函数检查我的到期日期当前时间 进程: 我正在使用jQuery UI日期时间选择器添加提醒日期时间,它给我这样的时间戳 2015-07-9 14:36 它没有 有任何秒,但我的created_at包含完整的Unix时间戳。

但使用下面的命令并没有给我任何结果。 使用PC日期和时间设置的MySQL日期时间。

MySql代码:

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

数据库:

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

到期列类型 varchar

PS:我正在制作提醒,想要查看我今天或现在有多少提醒。

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

2条回答 默认 最新

  • 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 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();
    
    打赏 评论

相关推荐 更多相似问题