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

On 21/06/14 03:12, Steve Crawford wrote:
> 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
>
Some SQL I wrote to explore this.

I think my solution does not have the above problems, but may have
others! :-)

Cheers,
Gavin

DROP TABLE IF EXISTS datex;

CREATE TABLE datex
(
id int PRIMARY KEY,
a_date date NOT NULL
);

INSERT INTO datex
(
id,
a_date
)
VALUES
(101, '2014-01-01'),
(102, '2014-01-02'),
(128, '2014-01-28'),
(129, '2014-01-29'),
(130, '2014-01-30'),
(131, '2014-01-31');

SELECT
(date_part('year', d.a_date)::text
|| '-'
|| (date_part('month', d.a_date) + 1)::text
|| '-1')::date
FROM
datex d;