Fetch data from mysql by converting it's timestamp to unix time

This will be done with PHP.

I basically want to get the number of rows that were inserted 30 minutes ago.

I have a time field on my table which is type TIMESTAMP and on update it's set to CURRENT_TIMESTAMP.

The date is stored in this format:

2011-05-27 04:29:17

My query is supposed to look something like this, however i just can't do it

SELECT COUNT(*) FROM my开发者_如何学编程table WHERE UNIX_TIMESTAMP(time) < '.time().'-1800

Where time() is PHP's function that fetches the UNIX time.

What it should basically do is print me the number of rows inserted from now to 30 minutes ago, but i just can't seem to make it work.

Can somebody help?

Small edit: Another problem i am seeing is that php's function time() displays the unix time which is UTC. The time stored in mysql is probably GMT i.e whatever my computer's time/timezone is set to.

You can easily get rows stored from now to 30 mins ago by simply using:

SELECT count(*) FROM mytable WHERE `time` >= DATE_SUB(UTC_TIMESTAMP, INTERVAL 30 minute)

Usage of UTC_TIMESTAMP is just an example if you're storing your date/time data as UTC_TIMESTAMP(), you can probably use NOW() if necessary, depends on what you're storing really.

**EDIT** Removed bad pointers and fixed example :)

1. Do you really need your computer's timezone to be different than UTC? why not just set it to UTC & save yourself the confusion? If that doesn't work, just use dateadd() on mysql to convert your mysql timestamp to UTC when checking?

2. My suggestion would be to write a small function to convert the mysql timestamp to your PHP timestamp format & load it into mysql. Then all you need to do is to call tmstamp(time_stamp) instead of time_stamp in your query. You can do the reverse too i.e. Convert PHP's "30 minutes ago" timestamp to mysql format and rerun your query (probably easier).

Usually it's just a formatting issue. It's not standardized across programs.