date now minus 30 minutes in mysql

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
potato
Forum Contributor
Posts: 192
Joined: Tue Mar 16, 2004 8:30 am
Location: my lovely trailer, next to the big tree

date now minus 30 minutes in mysql

Post by potato »

hi,

i have a mysql db where i got a timestamp field.
No i want to make a mysql query where he gives me the rows where the timestamp is between now and 30minutes ago.
I dont have many date mysql experiences, so any help would be great.

the timestamp is in the following format:

2006-12-16 18:24:36

thanx
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

You'd be best working with Unix timestamps for this.

Insert the date into the MySQL DB as a Unix timestamp using

Code: Select all

mktime
.

You can then run the query:

Code: Select all

$now = time();
$earlier = time - 1800;

Code: Select all

SELECT *
FROM somewhere
WHERE date < '$time'
AND date > '$earlier'
I assume there a way to do that without using PHP. But that's the first idea that hit my head.

Hope that helps, Stephen
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

impulse() wrote:You'd be best working with Unix timestamps for this.
Why use PHP when MySql can do it w/o php?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

hawleyjr wrote:
impulse() wrote:You'd be best working with Unix timestamps for this.
Why use PHP when MySql can do it w/o php?
I was just showing him a way to do it until somebody came along and showed him how to do it in MySQL :)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

According to
that would be

Code: Select all

SELECT
	x,y,z
FROM
	abc
WHERE
	datefield BETWEEN Now() AND Now()-Interval 30 minute
Post Reply