Postgresql date comparison not working

Question

I have a query problem in my postresql database. I try to get the last 100 logs after a certain time, but when I use this query:

select * from log_entry WHERE array['MESSAGE'] && tags AND CAST(last_updated AS DATE) >= '2013-02-28T16:47:26.394213' ORDER BY last_updated DESC LIMIT 100 

Here the last_updated column is of type: timestamp without a time zone

Sometimes I get magazines until '2013-02-28T16: 47: 26.394213', am I doing something wrong? Is there a better way to do this rather than using listing?

Thanks in advance!

Answer 1

(Sent from a comment on request.)

Well, here is your problem: when you jot down a time stamp (with or without a time zone) to a date, you trim the time part of the time stamp. Why not add '2013-02-28T16:47:26.394213' to the timestamp and compare it directly with last_updated ?

 select * from log_entry WHERE array['MESSAGE'] && tags AND last_updated >= '2013-02-28T16:47:26.394213'::timestamp without time zone ORDER BY last_updated DESC LIMIT 100 

Answer 2

use this. That should work.

select * from log_entry WHERE array ['MESSAGE'] && & tags & age (last_updated)

= age (now ()) ORDER BY last_updated DESC LIMIT 100