CODEWITHSUNDEEP
phpmysqldatetimetimestampunix-timestamp
hy_sultani
hy_sultani

Reputation: 27

MySQL compare expiry time stamp with current time 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.

Upvotes: 0

Views: 3013

Answers (2)

Hytool
Hytool

Reputation: 1368

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();

Upvotes: 0

Aman Aggarwal
Aman Aggarwal

Reputation: 18459

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());

Upvotes: 1

Related Questions