Filter timestamp by specific month-year

Question

I am new to postgres and would appreciate any advice. I have postgres table with a timestamp column whose values are in the format: 1970-01-01 00:00:00

My objective is to select records from the last three whole months - December 2016, January 2017 and February 2017. How would one write this query with only read access using SELECT?

When I start with:

SELECT to_char("start_time", 'YYYY-MM-DD HH:MM:SS') FROM trips;

Times are converted to AM/PM but I am only interested in extracting and subsetting by month and year

Answer 1

Here you go:

SELECT *
FROM trips
WHERE start_time BETWEEN '2016-01-01 00:00:00'::timestamp AND '2017-02-28 23:59:59'::timestamp;

Answer 2

You can use extract or date_trunc function to extract month in postgresql.

Very similar to question get last three month records from table

For more details about date time functions in postgresql use below link.

https://www.postgresql.org/docs/9.1/static/functions-datetime.html

Answer 3

Here is one method:

select t.*
from t
where start_date >= date_trunc('month',now() - interval '3' month) and
      start_date < date_trunc('month', now());