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

java, postgresql

Solution

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

Problem

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!

Original source