| From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
|---|---|
| To: | olly(at)lfix(dot)co(dot)uk |
| Cc: | Caleb Simonyi-Gindele <csg(at)bordervet(dot)ca>, pgsql-sql(at)postgresql(dot)org |
| Subject: | Re: Grouping by week |
| Date: | 2004-08-06 22:52:57 |
| Message-ID: | 24169.1091832777@sss.pgh.pa.us |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-sql |
Oliver Elphick <olly(at)lfix(dot)co(dot)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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Kenneth Gonsalves | 2004-08-07 04:00:30 | Re: surrogate key or not? |
| Previous Message | Josh Berkus | 2004-08-06 22:42:04 | Re: surrogate key or not? |