Query listing the dates

Question

Good night,

How Query can get the following action in Postgresql:

List the dates that fell on Sunday of the last 6 months based on the current date, regardless of the data in the database.

NOTE: The records that are in the database, is in date format, example: 2017-09-10

Answer 1

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

Since the version 8.4 of Postgresql, the generate_series() is capable of generating date series:

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

Answer 2

I did the following query, see if it helps you:

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 one

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

now, we take the current date, and subtract i days, taking all dates from today, up to 180 days ago.

finally on Where, we filter only the dates on which the day of the week, dow, is equal to 0 (Sunday).

links:

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

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

I put in Sqlfiddle: http://sqlfiddle.com/#! 17/9eecb/4258

Answer 3

I believe you can use this function:

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

date is my column containing 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.

Updating: I tested it here and you can filter it the way I informed you

Follow 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 the current date - 6 months

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