New PHP + PostgreSQL group on Google Groups2

Hello. I have created a new group in the Google Groups beta site for
PHP + PostgreSQL development.

This group is for questions on advanced web development with PHP and
PostgreSQL using Linux. Topics include functions, regular expressions,
classes (OOP), speed, security, editor customization, SQL, and
software installation/maintenance.

Everyone is welcome.

http://groups-beta.google.com/group/php-psql

Grouping by week

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



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Re: Grouping by week

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


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Re: Grouping by week

Oliver Elphick 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)