Query that lists the dates

I made the following query, see if it helps:

Select
    now()::date -  i 
FROM generate_series(0,6*30) i(i)
where EXTRACT('dow' from now()::date -  i) = 0

Explaining:

  

generate_series: Generate a series of values, from start to finish with a step size of

Using the parameters, 0 and 6 * 30 (6 months), a series from 0 to 180 will be generated.

Now, we get the current date, and subtract i days, having all dates starting today, up to 180 days ago.

Finally no where, we only filter the dates on which the day of the week, dow , equals 0 (Sunday).

links:

link

link

I put it in SQLFiddle: link

You can generate a series of dates using the generate_series() function, and then filter only the dates that fall on Sunday using the WHERE clause.

Since the 8.4 version of PostgreSQL, the generate_series() function is able to generate data series:

SELECT
    dt::date
FROM
    generate_series( now() - '6 month'::interval, now(), '1 day'::interval ) AS dt
WHERE
    EXTRACT('dow' from dt) = 0;

I think you can use this function:

The function in case returns the day of the week, 7 is Sunday.

data_admissao is my column that contains the dates.

SELECT EXTRACT(ISODOW FROM  data_admissao)

FROM tb_empregado
WHERE EXTRACT(ISODOW FROM  data_admissao) =7

The function: date_trunc('day', NOW() - interval '6 month') will return you the date 6 months ago.

Update: I tested it here and you can filter it the way I told you.

Here's an example:

SELECT *
FROM tb_empregado
WHERE EXTRACT(ISODOW FROM  data_admissao) =7 
AND data_admissao <date_trunc('day', NOW() - interval '6 month')

//Função responsável por retornar o dia da semana da data inserida, no caso a coluna
EXTRACT(ISODOW FROM  data_admissao) 

Party responsible for returning current date - 6 months

date_trunc('day', NOW() - interval '6 month')