Generate a list from atom and string

On Erlang, there is no string data type, but its a list of strings. So different with other language.
Also some function return an atom, which usually we need to combine it with our string. For example was the need to generate an sql query from an input parameter and our hard coded string.

Let say we generate a year from erlang fun, then from that year, we generate the month and data.
To do this properly are :

{{Year,_,_},_} = calendar:universal_time(),
Y1 = io_lib:format("~p-01-01",[Year]),
Y2 = lists:flatten(Y1),

Y2 result will be  "2018-06-18"


Hope this helps, as i look all around the google for this.

Read More

Posgresql reporting query

With PostgreSQL when doing with datetime for reporting, it realy help if the DB can aggregate your needed data rather than process it yourself.

I found this interesting and useful as i start needed query for reporting from my dataset.

For example i want to have a list of records which user register per month. With postgresql here is the query :

SELECT date_trunc('month', created_at),
       count(*)
FROM users
GROUP BY 1
ORDER BY 1 DESC;

With date_trunc PostgreqSQL do its magic and return you the needed data without doing a query per month. really not so effective query. To get per week activity :

with months as (
  select month
  from generate_series('2018-01-01'::date, now()::date, '1 month'::interval) month
)

SELECT months.month,
       count(pmactivitylogs.id)
FROM months,
left join pmactivitylogs on date_trunc('month', pmactivitylogs.created_at) = months.month

GROUP BY 1
ORDER BY 1 DESC;

We will get all the monthly count of records for our reporting.

For more details get the docs on postgresql web site.

Read More