1

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 ?

Improve this question

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ok if i am not wrong ::date would extract 2016-06-27 from user input value but ? and how i can add this date and set time(23:59:59) in other and clause of my query ?
I am sorry again, If you see my query again after where clause: where logtime between '2016-06-27 10:40' and '2016-06-27 23:59:59'. I am using " between ' ' and ' ' " how this assignment can be work as you write a query. Can you please share a complete query
That's the point of my first query, you don't need the BETWEEN thing. It is easier to simply work with dates.
0

If you want to use berween clause, you can try this

select * from users where logtime between in_date::timestamp and 
    (in_date::date || ' 23:59:59')::timestamp; --in_date is input date from html page

you can refer datetime in postgres for more details and timestamp manipulation.

Hope it helps.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.