Sometimes, we want to retrieve certain records which associated with certain timeframe. Supposed that we want to get the data let’s say between 3rd March 2016 - 27th May 2016, is there any way to do that kind of things ?.
Login first to MySQL console by typing the following command in the bash prompt :
mysql -uusername -p
We can see the output of the command execution as follows :
[username@hostname ~]$ mysql -uroot -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 137731 Server version: 5.6.27-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> desc table_sample; ERROR 1046 (3D000): No database selected mysql> use database_sample; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> desc table_sample; +------------+-------------+------+-----+---------------------+---+ | Field | Type | Null | Key | Default| Extra | | +------------+-------------+------+-----+---------------------+---+ | id | int(11) | NO | PRI | NULL | auto_increment | | username | varchar(20) | NO | MUL | NULL | | | ipaddress | varchar(15) | YES | | NULL | | | uploadtime | timestamp | NO | | 0000-00-00 00:00:00 | | filename | varchar(40) | NO | | NULL | | +------------+-------------+------+-----+---------------------+---+ 5 rows in set (0,00 sec) mysql>
In the above example, we have a database named database_sample. Within the database, we have a table named table_sample. This table consists several fields which is one of the field has its type timestamp. As we already knew, timestamp is a type of field in MySQL database correlates with date and time. This table is the table which is going to be used for an example to retrieve records of data within a certain timeframe.
So, if we want to retrieve records of data based on time we have to adjust the query using several reserved keywords query language in MySQL database for the purpose of finding records of data within a timeframe.
And for the answer of the question above, certainly there is a way to do that by executing certain query. The pattern is shown below :
SELECT * FROM table_name WHERE field_with_timestamp_type BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd';
We will give an example based on the above query pattern as follows :
SELECT username,ipaddress,uploadtime,filename FROM table_sample WHERE uploadtime between '2016-03-03' AND '2016-05-27';
The above query executed in MySQL command console will return all the files uploaded specifically between 3rd March 2016 and 27th May 2016.
mysql> select username,uploadtime,filename from table_sample where uploadtime between '2016-03-03' AND '2016-05-27'; +----------+---------------------+--------------------------------+ | username | uploadtime | filename | +----------+---------------------+--------------------------------+ | steven | 2016-03-05 12:51:09 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | patrick | 2016-03-05 12:51:52 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | john | 2016-03-06 20:24:10 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | dale | 2016-03-06 20:25:15 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | tom | 2016-03-08 19:34:01 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | rick | 2016-03-14 10:16:50 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | sam | 2016-04-09 08:38:27 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | dale | 2016-04-09 08:52:39 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | robert | 2016-04-09 09:19:08 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | thomas | 2016-04-09 09:19:37 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | wayne | 2016-05-09 14:55:12 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | jeff | 2016-05-09 14:55:48 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | pamela | 2016-05-12 10:20:47 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | | julia | 2016-05-12 10:21:51 | xxxxxxxxxxxxxxxxxxxxxxxxxx.zip | +----------+---------------------+--------------------------------+ 14 rows in set (0,00 sec) mysql>