0

how to show data from database in 10 hours ago

ex: Article
id | title | date ( using datetime) |
1 | data1 | 2014-02-1 01:00:00
2 | data2 | 2014-02-1 07:00:00
3 | data3 | 2014-02-1 11:00:00
4 | data4 | 2014-02-1 12:00:00
Now = 2014-02-1 12:00:00

query select * from Article where date < 10 hours ago from now()

Output :

2 | data2 | 2014-02-1 07:00:00
3 | data3 | 2014-02-1 11:00:00
4 | data4 | 2014-02-1 12:00:00

3
Contributors
4
Replies
21
Views
3 Years
Discussion Span
Last Post by effu
0

Sure, timestapmdiff() is a function of MySQL that returns the difference between two dates. By the way, there was an error in my previous example, the comparision operator is <= not =<.

This function takes 3 arguments: the first argument is the unit and can assume the value of: year, quarter, month, week, day, hour, minute, second, microsecond. The other two arguments are the datetime expressions: these can be strings representing a timestamp or datetime column types. For example:

> select timestampdiff(hour, '2014-02-10 23:30', '2014-02-11 11:30') as difference;

+------------+
| difference |
+------------+
|         12 |
+------------+
1 row in set (0.00 sec)

As you see you get an integer, this can be signed i.e. it can be negative if you reverse the values, for example:

> select timestampdiff(hour, '2014-02-11 11:30', '2014-02-10 23:30') as difference;

+------------+
| difference |
+------------+
|        -12 |
+------------+
1 row in set (0.00 sec)

In my previous post:

select id, title, date from article where timestampdiff(hour, date, now()) <= 10;

I used the date column of your table as second argument and the now() function as third argument and then the comparison operator <= (less than or equal) to check the results and select only those matching the condition: less than or equal to 10.

An example table:

create table article(
    id int unsigned not null primary key auto_increment,
    title varchar(200) not null,
    date datetime not null
    );

insert into article (title, date) values('data 1', '2014-02-09 18:00');
insert into article (title, date) values('data 2', '2014-02-10 23:30');
insert into article (title, date) values('data 3', '2014-02-11 06:45');
insert into article (title, date) values('data 4', '2014-02-11 09:10');

A simple select returns:

> select * from article;
+----+--------+---------------------+
| id | title  | date                |
+----+--------+---------------------+
|  1 | data 1 | 2014-02-09 18:00:00 |
|  2 | data 2 | 2014-02-10 23:30:00 |
|  3 | data 3 | 2014-02-11 06:45:00 |
|  4 | data 4 | 2014-02-11 09:10:00 |
+----+--------+---------------------+
4 rows in set (0.00 sec)

While the previous example will return only two rows:

> select * from article where timestampdiff(hour, date, now()) <= 10;
+----+--------+---------------------+
| id | title  | date                |
+----+--------+---------------------+
|  3 | data 3 | 2014-02-11 06:45:00 |
|  4 | data 4 | 2014-02-11 09:10:00 |
+----+--------+---------------------+
2 rows in set (0.00 sec)

Hope it's more clear now and sorry for the previous mistake :)

Documentation:

0

Another method to use is mysql DATE_SUB()

Try this method:

select * from Article where date > DATE_SUB(NOW(),INTERVAL 10 HOUR)

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.