CODEWITHSUNDEEP
phptimestampfirebird
rockstardev
rockstardev

Reputation: 13537

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?

Upvotes: 2

Views: 2343

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109171

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.

Upvotes: 3

Adder
Adder

Reputation: 5878

You can use the UNIX_TIMESTAMP function. Be aware though that php and mysql may disagree, both a few seconds-wise as timezone-wise.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_unix-timestamp

SELECT * FROM mytable where UNIX_TIMESTAMP(mytime) > 1369157557

Upvotes: 0

Related Questions