timestamp differences

Anon

Dear all,+

I have a table in mysql storing a log for an application. In this table there is a column with the datatype timestamp.
I want to know all rows which have been inserted within the last 5 minutes. I tried to use the "time_to_sec" function combined with the "now" function, but it did not produce proper results.

Is there anybody who has had an idea?

Thank you very much for your interest!

Cheers,

Matthias

yendor

I've never tried this but you'll have to let me know if it works.

SELECT * FROM table WHERE timestamp_column_name >= NOW()-500;

The NOW()-500 should give a timestamp for 5 minutes ago.

Anon

Dear Rodney,

thank you for your information but it doesn't function properly.
If the timestamp is near of the next day, then the function will fail. Because time of the next day starts with (0:00:00) and the time of the old day ends with (23:59:59).

Do you have a workaround for that?

Thank you very much for your help!

Best regards,

Matthias

yendor

Sorry I got the timestamps mixed up! I would try a unix_timestamp() function since it works on seconds from the epoch. Let me know if this works.

SELECT * FROM table WHERE UNIX_TIMESTAMP(date_from_table) >= UNIX_TIMESTAMP - 300;

This should decrease the current timestamp by 300 seconds(5 min). Since both timestamps will be in seconds since 01/01/1970 it shouldn't matter if it's a new day or not.

yendor

Sorry I forgot the last ()!

SELECT * FROM table WHERE UNIX_TIMESTAMP(date_from_table) >= UNIX_TIMESTAMP() - 300;