Linux, FreeBSD, Juniper, Cisco / Network security articles and troubleshooting guides
https://forum.ivorde.com/

Mysql> select records that have date/datetime column equal to today's date
https://forum.ivorde.com/mysql-select-records-that-have-date-datetime-column-equal-to-today-s-date-t974.html
Page 1 of 1

Author:  admin [ Tue Nov 22, 2011 9:40 am ]
Post subject:  Mysql> select records that have date/datetime column equal to today's date

How to select rows from a mysql table containing multiple records, all of them having a datetime column.

Example mysql table:
Code:
mysql> select * from info;
+---------+---------------------+------------+
| id | upload_date    | ip    |
+---------+---------------------+------------+
|       1 | 2011-11-21 21:07:29 | 0123456789 |
|       2 | 2011-11-21 21:15:07 | 0123456789 |
|       3 | 2011-11-22 14:26:49 | 0123456789 |
+---------+---------------------+------------+
3 rows in set (0.00 sec)


Selecting rows with datetime column equal to today's date:
Code:
mysql> select * from info where date(upload_date) = date_sub(curdate(), interval 0 day);
+---------+---------------------+------------+
| id | upload_date    | ip    |
+---------+---------------------+------------+
|       3 | 2011-11-22 14:26:49 | 0123456789 |
+---------+---------------------+------------+
1 row in set (0.00 sec)


and, obviously, selecting all rows with one day before, use the "1 day" interval.

The mysql date() function is used on the table column because the column type is datetime (hour/min/second) and the date_sub() and curdate() functions are "date" functions.

Page 1 of 1 All times are UTC - 5 hours [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/