Search for current month data in postgresql 9.2

Question

I'm trying to get the ticket data only from the current month on a PostgreSql 9.2 database.

The field called data_cadastro is DATETIME:

 id_ticket |    data_cadastro
-----------+---------------------
      2521 | 2017-10-31 08:11:48

how should I do ?

Answer 1

This is what you need:

SELECT *
FROM ticketdata
WHERE data_cadastro >= date_trunc('month', CURRENT_DATE);

Answer 2

Just use extract

test=# SELECT extract(month FROM '2017-10-31 08:11:48'::timestamp);
 date_part 
-----------
        10
(1 row)

If you want all tickets for that month,

SELECT *
FROM ticketdata
WHERE extract(month FROM data_cadastro) = 10;

If you want only that year and month, just do this.

SELECT *
FROM ticketdata
WHERE date_trunc('month', data_cadastro)::date = '2017-10-01'::date;

Answer 3

You can do something like this

SELECT *
FROM tickets
WHERE data_cadastro BETWEEN date_trunc('month', CURRENT_DATE) AND (date_trunc('month', CURRENT_DATE) + interval '1 month - 1 second')

or

SELECT *
FROM tickets
WHERE extract (month FROM data_cadastro) = extract (month FROM CURRENT_DATE)