pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGrouping by week
3+ messages / 3 participants
[nested] [flat]
* Grouping by week
@ 2004-08-06 21:29 Caleb Simonyi-Gindele <csg@bordervet.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Caleb Simonyi-Gindele @ 2004-08-06 21:29 UTC (permalink / raw)
To: pgsql-sql
I'm using
SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY
trans_date
and it is being used to group sales results by week. It works really well.
What I'm wondering is if I can shift the week from a Mon-Sun
articulation(default with Postgre) to a Sun-Sat sequence. I need it that way
in order to comply with a legacy stats system.
Thanks,
Caleb
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Grouping by week
@ 2004-08-06 22:08 Oliver Elphick <olly@lfix.co.uk>
parent: Caleb Simonyi-Gindele <csg@bordervet.ca>
0 siblings, 1 reply; 3+ messages in thread
From: Oliver Elphick @ 2004-08-06 22:08 UTC (permalink / raw)
To: Caleb Simonyi-Gindele <csg@bordervet.ca>; +Cc: pgsql-sql
On Fri, 2004-08-06 at 22:29, Caleb Simonyi-Gindele wrote:
> I'm using
>
> SELECT EXTRACT(WEEK FROM trans_date), SUM(tran_amount) ... GROUP BY
> trans_date
>
> and it is being used to group sales results by week. It works really well.
>
> What I'm wondering is if I can shift the week from a Mon-Sun
> articulation(default with Postgre) to a Sun-Sat sequence. I need it that way
> in order to comply with a legacy stats system.
How about:
SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL)
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"Be still before the LORD and wait patiently for him;
do not fret when men succeed in their ways, when they
carry out their wicked schemes."
Psalms 37:7
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Grouping by week
@ 2004-08-06 22:52 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oliver Elphick <olly@lfix.co.uk>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2004-08-06 22:52 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Caleb Simonyi-Gindele <csg@bordervet.ca>; pgsql-sql
Oliver Elphick <olly@lfix.co.uk> writes:
> How about:
> SELECT EXTRACT(WEEK FROM trans_date + '1 day'::INTERVAL)
Note that if trans_date is actually a date, you are much better off just
adding an integer to it:
SELECT EXTRACT(WEEK FROM trans_date + 1)
If you add an interval then the date will be promoted to a timestamp,
and all of a sudden you have possible issues with funny behavior at
DST boundaries.
I think since 7.3 the DST issue is only serious if trans_date is
actually stored as timestamp with time zone, but it has been able to
bite you in the past.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2004-08-06 22:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-06 21:29 Grouping by week Caleb Simonyi-Gindele <csg@bordervet.ca>
2004-08-06 22:08 ` Oliver Elphick <olly@lfix.co.uk>
2004-08-06 22:52 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox