Good staff I have a table with date column of certain records.
I know to add up the records for 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 values from column1 of the current month only.
Any suggestions?
You can truncate a date in Postgres with date_trunc . In a simple way it lets you ignore a part of the date / time and focus only on the part you want.
An example usage:
SELECT date_trunc('month', current_date) as este_mes;
este_mes
------------------------
2014-04-01 00:00:00-03
See that it truncated today's date, and the result was the first day of this month because the month keyword was used, but you can truncate in the hour, minute, or even year. It's a good alternative so you do not have to use the fuction 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 time and date manipulation functions, see link .