user526206 user526206 - 1 year ago 77
SQL Question

How to set time in query in postgres

I have a query in which i want to select records for a single day only. like b/w '00:00:00' to '23:59:59'.

for example if user select data and time(2016-06-27 10:40) then my query would be like:

select * from users where logtime between ? and '2016-06-27 23:59:59' // logtime is a timestamp

As per requirement on html page i only need to give an option that user can select a single date and time and then submit a search. I am confusing that in a sql query how i can append a end date(whatever date user select) and time(always be 23:59:59).
Can someone give me idea is there any postgres function which can help to extract a date from given date and append time(23:59:59) and return a datetime ?

Answer Source

You can extract a date from a timestamp by simply casting the timestamp to date. So if I understand you problem correctly, you should something like that:

select * from users where logtime::date='2016-06-27 10:40'::date;

Edit: If you really want to simply extract the date to combine it with a different timestamp you can do it by manipulating the timestamp as a string, eg:

SELECT ('2016-06-27 10:40'::date || ' 23:59:59')::timestamp;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download