SQL query specify date time record SQL statement and query instance

SQL query specify date time record SQL statement and query instance

First create a table ABC structure is as follows

CREATE TABLE ' cc '. ' Loup ' (
' ID ' INT (4) Not NULL auto_increment,
' DateTimes ' INT (4) NULL,
' IPAddress ' VARCHAR NULL,
PRIMARY KEY (' id ')
) ENGINE = MYISAM

Then BULK INSERT data

INSERT into ' loup ' (' id ', ' datetimes ', ' IPAddress ') VALUES
(1, 1271865600, ' 127.0.0.1 '),
(2, 1271865600, ' 127.0.0.1 '),
(3, 1271865600, ' 127.0.0.1 '),
(4, 1271865600, ' 127.0.0.1 '),
(5, 1271865600, ' 127.0.0.1 '),
(6, 1271865600, ' 127.0.0.1 '),
(7, 1271952000, ' 192.168.0.1 '),
(8, 1271952000, ' 192.168.0.1 '),
(9, 1271952000, ' 192.168.0.1 '),
(10, 1271952000, ' 192.168.0.1 ');

All right, we're ready for the 2010-04-22 data.

Note: 1271865600 =2010-04-23
1271952000 =2010-04-23

*/

Database Tutorial Connections

MySQL tutorial _connect ("localhost", "root", "root") or Die (' Database Server Uid or Password error! ');
mysql_select_db (' cc ') or Die (' Data! ');
mysql_query ("Set NAMES ' gb2312 '");/set the document in line with the database code OK, otherwise there will be garbled

$currentTime = Mktime (0,0,0,04,22,2010);

Using Mktime to read the 2010-04-22 time cut

$sql = "Select * from Loup where datetimes= $currentTime";
$query = mysql_query ($sql);
while ($rs = Mysql_fetch_array ($query))
{
echo $rs [0], ' | ', $rs [1], ' | ', $rs [2], "<br/>";
}

/*
Output results are

1|1271865600|127.0.0.1
2|1271865600|127.0.0.1
3|1271865600|127.0.0.1
4|1271865600|127.0.0.1
5|1271865600|127.0.0.1
6|1271865600|127.0.0.1

Summarize:
When designing a database specifically for a time date we'd better use the mktime timestamp to save the data for easy query and subtraction operations, with INT (4) enough to save the current time.

This site original article reproduced annotated source www.111cn.net China web first stop