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

On Fri, Jun 20, 2014 at 3:42 PM, Arup Rakshit
<aruprakshit@rocketmail.com> wrote:

How can I get first day date of the previous month. Last day of previous
month can be found using the answer -
http://stackoverflow.com/a/8945281/2767755

Here is how to get the first day date of the previous month:
=# select date_trunc('month', now()) - interval '1 month' as last_month;
last_month
------------------------
2014-05-01 00:00:00+09
(1 row)
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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... 
 
Regards,
Arup Rakshit

On Friday, 20 June 2014 12:22 PM, Michael Paquier <michael.paquier@gmail.com> wrote:

On Fri, Jun 20, 2014 at 3:42 PM, Arup Rakshit

<aruprakshit@rocketmail.com> wrote:

How can I get first day date of the previous month. Last day of previous
month can be found using the answer -
http://stackoverflow.com/a/8945281/2767755

Here is how to get the first day date of the previous month:
=# select date_trunc('month', now()) - interval '1 month' as last_month;
      last_month
------------------------
2014-05-01 00:00:00+09
(1 row)
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 20 June 2014 09:11, Arup Rakshit <aruprakshit@rocketmail.com> 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...

It is.

You can also do it like this to get the first day of each of the last 6 months:

=# select date_trunc('month', now()) - interval '1 month' * n from
generate_series(1, 6) as i(n);
?column?
------------------------
2014-05-01 00:00:00+02
2014-04-01 00:00:00+02
2014-03-01 00:00:00+01
2014-02-01 00:00:00+01
2014-01-01 00:00:00+01
2013-12-01 00:00:00+01
(6 rows)

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

On Friday, June 20, 2014 08:12:14 AM you wrote:

Welcome. And yes, it is awesome.

I agree.

Being new to the DB

No. I worked on Oracle DB earlier days(approx 2 years). But pgsql just 1
month.

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.

Yes, you are correct. Otherwise it is very hard to follow.

One suggestion I need from you. Would it be a good to start straight from
doco, or should I start from a book ?

Again thanks for writing .

--
================
Regards,
Arup Rakshit
================
Debugging is twice as hard as writing the code in the first place. Therefore,
if you write the code as cleverly as possible, you are, by definition, not
smart enough to debug it.

--Brian Kernighan

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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;

On Sat, Jun 21, 2014 at 12:54 PM, Arup Rakshit
<aruprakshit@rocketmail.com> wrote:

One suggestion I need from you. Would it be a good to start straight from
doco, or should I start from a book ?

It depends on what you are looking for and what you want to learn, but
personally, as the documentation of Postgres is well-maintained, it is
usually enough to refer to it when looking for solutions of given
problems. Newcomers may be lost at first sight as it has a lot of
content, but it is well-organized and logically organized. On top of
the docs, I've found the Postgres wiki to be quite handy when looking
for things more specific that are not directly mentioned in the docs:
https://wiki.postgresql.org/.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general