|
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;
|
