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