Determine if a given timestamp is within the same day in postgresql

Question

I've got a PostgreSQL table with a "timestamp without time zone" field. At some given point I query this table, and I need those records where the timestamp is within the same day as when the query is performed. Here is the code:

 DateTimeFormatter formatter = DateTimeFormat.forPattern("yyyy/MM/dd HH:00:00");
 DateTime dt = new DateTime();
 String timeStampAsString = dt.toString(formatter);
 String select = "SELECT * FROM " + tableName + " WHERE " + tableName + ".timestamp >= '"+ timeStampAsString + "'";
 pst = dbConnection.prepareStatement(select);
 pst.execute();

I'm using Joda here as you can see, also I populate that table in some other point, so I now the format is correct (yyyy/MM/dd HH:00:00). Not really sure if I could pass a long directly to the query instead of converting to String, but this does not bother me right now.

Since the query condition is >= than "NOW" timestamp, this is very unlikely it returns something. What I need is to get the records within the same day as the "NOW" timestamp. I suppose I have to ask for upper and lower bounds, but I'm not sure how to calculate the lower boundary for this query...any ideas?

Thanks!

Answer 1

I think you need this query

select *
from test1
where ts >= current_date and ts < current_date + interval '1 day';

or

select *
from test1
where ts >= now()::date and ts < now()::date + interval '1 day';

it's also possible to do

select *
from test1
where ts::date = now()::date;

but I think it will perform worse because conversion of data in column.

sql fiddle demo