Postgresql - Add existing values for the current month

Asked

Viewed 976 times

1

Good staff I have a table with column dates of certain records.

I know that to sum up the records of a given day: 

SELECT SUM(CAST(REPLACE(coluna1,',','.') AS DOUBLE PRECISION)) FROM tabela1 where CAST(data as date)=current_date and xxxxx='111111'

I needed at this point a function that only adds up column values1 for the current month..

Any suggestions?

sql postgresql

1 answer

0


You can truncate a date in Postgres with the function date_trunc. In a simple way it allows you to ignore a part of the date/time and focus only on the part you want.

An example of use:

SELECT date_trunc('month', current_date) as este_mes;
        este_mes       
------------------------
 2014-04-01 00:00:00-03

Note that he truncated today’s date and the result was the first day of this month because the keyword was used month, but you can truncate on time, minute or even year. It’s a good alternative so you don’t need to use the fução extract more than once.

So you can use this feature for your query more or less like this:

SELECT sum(...) as soma 
FROM ... 
WHERE date_trunc('month', data) = date_trunc('month', current_date);

For more date and time manipulation functions see http://www.postgresql.org/docs/current/static/functions-datetime.html.

Browser other questions tagged sql postgresql

You are not signed in. Login or sign up in order to post.