Page 1 of 1

date now minus 30 minutes in mysql

Posted: Mon Dec 11, 2006 4:58 pm
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

Posted: Mon Dec 11, 2006 5:07 pm
by timvw

Posted: Mon Dec 11, 2006 5:11 pm
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

Posted: Mon Dec 11, 2006 7:30 pm
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?

Posted: Mon Dec 11, 2006 7:42 pm
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 :)

Posted: Tue Dec 12, 2006 1:17 am
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