How to compare Firebird Database TIMESTAMP with PHP time() value?

My database has a data field of type TIMESTAMP. It’s called “mytime”. I get a number from php’s time() function. e.g:

print time();

Then I query the db:

SELECT * FROM mytable where mytime > 1369157557

It complains:

conversion error from string "1369157557"

I’m guessing you can’t compare a PHP timestamp with a Firebird timestamp. Why not? And how do I get around it?

Answer

There are two options: convert that unix timestamp to a Firebird timestamp before comparison. Or do something like described in this message on the Firebird-Support list:

select DATEDIFF(second, timestamp '1/1/1970 00:00:00', current_timestamp)
from rdb$database

Or specifically in your query:

SELECT * 
FROM mytable 
where DATEDIFF(second, timestamp '1/1/1970 00:00:00', mytime) > 1369157557

Just keep in mind that the times stored in your database may not be stored in UTC, and in that case you need to take the offset against UTC into consideration as well.