Selecting by Month in Postgresql

Selecting by month in PostgreSQL

You can use EXTRACT function, like this:

SELECT id, name, birthday FROM employee.person 
WHERE EXTRACT(MONTH FROM birthday) > 10;

Your problem comes from the fact that there is no such thing as Month function in PostgreSQL. Check online documentation here to see what you can get instead. Extract should be enough.

Query a dataset applying filter on month and year in PostgreSQL

Try this:

SELECT * FROM my_table 
WHERE EXTRACT('month' from  the_debt_paid) = 6 
and EXTRACT('year' from the_debt_paid)=2019

You can write your query like this also:

SELECT * FROM my_table 
WHERE (EXTRACT(month from  the_debt_paid), EXTRACT('year' from the_debt_paid))=(6,2019)

you can compare month and year by using extract
DEMO

How do I select between 1st day of the current month and the 1st day of the following month (from current month) in postgres

Your can use the date_trunc() function, see the manual. The result is :

SELECT *
FROM tbl 
WHERE tbl.createdate > date_trunc('month', Now()) 
  and tbl.createdate < (date_trunc('month', Now()) + interval '1 month')

Fetch records of current month using PostgreSQL query

You can compare the month and year of a date with the current one. But the index by field will not be used, you can build a separate index by year and month for this.

select *
from your_table
where extract(YEAR FROM createdAt) = extract(YEAR FROM now())
      and extract(MONTH FROM createdAt) = extract(MONTH FROM now())

PostgreSQL: Select only past three months of data

As documented in the manual, there is no datediff() function in Postgres. You just subtract an interval:

select *
from cte_data
where my_timestamp >= current_timestamp - interval '3 month';

Postgresql select between month range

This is a very common need in reporting environments. I have created several functions to accommodate these date manipulations

CREATE OR REPLACE FUNCTION public.fn_getlastofmonth (
  date
)
RETURNS date AS
$body$
begin
    return (to_char(($1 + interval '1 month'),'YYYY-MM') || '-01')::date - 1;
end;
$body$
LANGUAGE 'plpgsql'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

Then you can use...

WHERE date >= '2012-01-01' 
  AND date < fn_getlastofmonth('2013-04-01') 

How to extract year and month from date in PostgreSQL without using to_char() function?

date_part(text, timestamp)

e.g.

date_part('month', timestamp '2001-02-16 20:38:40'),
date_part('year', timestamp '2001-02-16 20:38:40') 

http://www.postgresql.org/docs/8.0/interactive/functions-datetime.html

---

Related Topics