How to Query Date and Time

If you want to obtain data from any specific date, just put the date in the format ‘mm/dd/yyyy’. If the column data type is timestamp, you can cast to date with ‘::date’

select * from sales where sold_at::date = ‘01/20/2021’;

If you want to obtain the sales done on each day in a specific period of time, for example between 11:00 am and 12:00 am, you can cast the timestamp to time and filter in a range of time. Then we can group by date.

select sold_at::date, sum(revenue) 
from sales 
where sold_at::time between '11:00'::time AND '12:00'::time
group by 1 order by 1;

IN THIS PAGE