Postgres Pro
Downloads
Home   >   mailing lists

Thread: New PHP + PostgreSQL group on Google Groups2

New PHP + PostgreSQL group on Google Groups2

From
dguarneri@satx.rr.com (N. David)
Date:
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

From
Caleb Simonyi-Gindele
Date:
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

Re: Grouping by week

From
Oliver Elphick
Date:
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
mensucceed in their ways, when they     carry out their wicked schemes."                            Psalms 37:7

Re: Grouping by week

From
Tom Lane
Date:
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