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
Hi,
if you're using MySQL then there is the timestampdiff() function, for example:
select id, title, date from article where timestampdiff(hour, date, now()) =< 10;
Docs: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_timestampdiff
can you explain about that script please cereal?
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:
Another method to use is mysql DATE_SUB()
Try this method:
select * from Article where date > DATE_SUB(NOW(),INTERVAL 10 HOUR)
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.