Re: How can I get first day date of the previous month ?

On 06/20/2014 12:11 AM, Arup Rakshit wrote:
> Thanks for your answer. How to get the first day date of last 6 months
> from now then will be :
>
> yelloday_development=# select date_trunc('month', now()) - interval '5
> month' as first_month;
> first_month
> ---------------------------
> 2014-01-01 00:00:00+05:30
> (1 row)
>
> Is it correct ? I am new pgdql DB :-) Awesome DB it is...
>
Welcome. And yes, it is awesome. Being new to the DB and mailing list,
please note that the convention on all PostgreSQL mailing lists is to
post your reply at the bottom and not to top-post.

The solution you gave will work but I'll offer a word of caution - date
and time manipulation can get tricky and even the way it is handled in
PostgreSQL has occasionally been tweaked between versions (a good reason
to always read the release notes).

The three things that seem to cause the most confusion are time-zones,
daylight saving time and irregular intervals. So if you assume that one
day is 24 hours you can encounter trouble at DST changes. And
PostgreSQL, like any system that manipulates time, needs to make certain
assumptions about what an interval means (what is one month before March
31) which can lead to this:

steve=> select '2014-03-31'::date - '1 month'::interval + '1
month'::interval;
---------------------
2014-03-28 00:00:00

when you might have expected this:

steve=> select '2014-03-31'::date - ('1 month'::interval + '1
month'::interval);
---------------------
2014-01-31 00:00:00

Have fun but read the docs, experiment and test - especially with dates
and times.

Cheers,
Steve