how to subtract two DATETIME values

[deepbright]

I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).

Any help?

[bartonc]

I'm having trouble understanding the MySQL docs on how to subtract two DATETIME values. I have two tables that have a DATETIME column. In my SELECT I am doing a JOIN to bring in both sets of rows. What I want is to subtract the DATETIME values to determine the number of seconds between their time. In the WHERE clause I also want to filter the selected rows if the difference in the DATETIME values is less that 900 (seconds).

Any help?

I found one way to make dates subtractable:

SELECT unix_timestamp(now()) - unix_timestamp('2007-03-19 09:50:00');

[code green]

You need something like this

To subtract the two DATETIME fields use
TIMESTAMPDIFF(SECOND,field1,field2)

To calculate if you have 900 seconds use
SELECT `columns` FROM `table`
WHERE DATE_SUB(value INTERVAL 900 SECOND)

Put together
SELECT `columns` FROM `table`
WHERE DATE_SUB((TIMESTAMPDIFF(SECOND,field1,field2) INTERVAL 900 SECOND)

Not tested this but it looks on the right lines