converting a specified year and week into a date

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

You can extract the week from a given date with this:

SELECT EXTRACT(WEEK FROM '2006-01-01'::date);

Be careful, the 1.1. can be in the 52. week in the last year. If you
know the first day in the year in week 1, then you can add 7* the given
week-1 days to this date.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 01:36, A. Kretschmer wrote:

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

You can extract the week from a given date with this:

SELECT EXTRACT(WEEK FROM '2006-01-01'::date);

Be careful, the 1.1. can be in the 52. week in the last year. If you
know the first day in the year in week 1, then you can add 7* the given
week-1 days to this date.

I think she wants to do the opposite: cast 2004w1 to YYYY-MM-DD format.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0sHFS9HxQb37XmcRAqClAJ4zkTJU7hT4vSbNM/8HyRqJwbSc1wCfeGJk
Tqr6q1YDe+TajGEY50Bl26Y=
=8i3I
-----END PGP SIGNATURE-----

am Wed, dem 14.02.2007, um 2:01:09 -0600 mailte Ron Johnson folgendes:

i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

You can extract the week from a given date with this:

SELECT EXTRACT(WEEK FROM '2006-01-01'::date);

Be careful, the 1.1. can be in the 52. week in the last year. If you
know the first day in the year in week 1, then you can add 7* the given
week-1 days to this date.

I think she wants to do the opposite: cast 2004w1 to YYYY-MM-DD format.

I know, but to do this do you need to know the first day in this week...

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 02:13, A. Kretschmer wrote:

am Wed, dem 14.02.2007, um 2:01:09 -0600 mailte Ron Johnson folgendes:

i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

You can extract the week from a given date with this:

SELECT EXTRACT(WEEK FROM '2006-01-01'::date);

Be careful, the 1.1. can be in the 52. week in the last year. If you
know the first day in the year in week 1, then you can add 7* the given
week-1 days to this date.

I think she wants to do the opposite: cast 2004w1 to YYYY-MM-DD format.

I know, but to do this do you need to know the first day in this week...

But she does not know the first day of the week.

A lookup table would solve OP's question. You'd have to populate
it, though. Shouldn't be too hard.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0sjCS9HxQb37XmcRAmeZAJ9gOnxOEIpax+bFgdIQUNxuKwgl/QCfZ5Mt
N5+z1KZqRqilpq0HdTVFlLA=
=ZJE5
-----END PGP SIGNATURE-----

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

I have found this little function, not realy what you want but trivial to
adapt to your problem:
(it returns a string with first and last day of the week)

create or replace function get_week(IN jahr int, IN kw int) returns text as $$
declare
datum date;
ret text;
begin
datum = (jahr || '-01-01')::date;

loop
exit when extract(dow from datum) = 4;
datum = datum + '1day'::interval;
end loop;
ret = to_char(datum+(7*(kw-1)-3||'days')::interval,'dd-mm-yyyy') || ' - ' || to_char(datum+(3+7*(kw-1)||'days')::interval,'dd-mm-yyyy');
return ret;
end;
$$ language plpgsql immutable strict;

test=*# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
(1 row)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 02:52, A. Kretschmer wrote:

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

[snip]

test=*# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
(1 row)

Is that week #2?

If weeks start on Sunday (which is what they do in the US), then
week #2 would either start on 04-Jan or 11-Jan (depending on whether
the 01-Jan partial week is considered week #1 or week #0).

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0tH5S9HxQb37XmcRAkcwAJkBy2zGzsOoHQYMWpQyy/gWkFMrYwCgvSPh
62eczkEjSH9hf/CqCmHLBzQ=
=bhxF
-----END PGP SIGNATURE-----

am Wed, dem 14.02.2007, um 3:10:17 -0600 mailte Ron Johnson folgendes:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 02:52, A. Kretschmer wrote:

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

[snip]

test=*# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
(1 row)

Is that week #2?

If weeks start on Sunday (which is what they do in the US), then
week #2 would either start on 04-Jan or 11-Jan (depending on whether
the 01-Jan partial week is considered week #1 or week #0).

Depends, there are different definitions. I have a calendar here and in
this the 2. week 2007 starts on monday, 08-01-2007.

It's like with http://personal.ecu.edu/mccartyr/isowdcal.html, but i
know, in america weeks starts with sunday.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 03:33, A. Kretschmer wrote:

am Wed, dem 14.02.2007, um 3:10:17 -0600 mailte Ron Johnson folgendes:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/14/07 02:52, A. Kretschmer wrote:

am Tue, dem 13.02.2007, um 13:56:15 -0800 mailte vanessa folgendes:

[snip]

test=*# select get_week(2007,2);
get_week
-------------------------
08-01-2007 - 14-01-2007
(1 row)

Is that week #2?

If weeks start on Sunday (which is what they do in the US), then
week #2 would either start on 04-Jan or 11-Jan (depending on whether
the 01-Jan partial week is considered week #1 or week #0).

Depends, there are different definitions. I have a calendar here and in
this the 2. week 2007 starts on monday, 08-01-2007.

Brown paper bag time: I was looking at the February calendar and
"seeing" January...

It's like with http://personal.ecu.edu/mccartyr/isowdcal.html, but i
know, in america weeks starts with sunday.

Interesting web site.

The ISO 8601 rule is: The first week of the year is the
week containing the first Thursday.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFF0t4JS9HxQb37XmcRArseAJ44Qrh9Jf+GrZoCxKbytbgC+bvbaACgo0sM
Tsqq67zsD6oCWukP6B7hjYk=
=kYtL
-----END PGP SIGNATURE-----

vanessa wrote:

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

thanks
vanessa :)

I think you're looking for this:

select to_date('01 2004', 'WW YYYY');
to_date
------------
2004-01-01
(1 row)

select to_date('02 2004', 'WW YYYY');
to_date
------------
2004-01-08
(1 row)

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //

am Wed, dem 14.02.2007, um 11:53:09 +0100 mailte Alban Hertroys folgendes:

vanessa wrote:

hi guys,
i was just wondering if it was at all possible to turn a year and a given
week number into a real date just using postgresql commands?

e.g. if i have year = 2004 and week = 1,
can i turn that into say 2004-01-01 (so that the specified
date is the one for the beginning of week 1 in the year 2004

thanks
vanessa :)

I think you're looking for this:

select to_date('01 2004', 'WW YYYY');
to_date
------------
2004-01-01
(1 row)

cool ;-)

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net

Am Mittwoch, 14. Februar 2007 11:53 schrieb Alban Hertroys:

I think you're looking for this:

select to_date('01 2004', 'WW YYYY');
to_date
------------
2004-01-01
(1 row)

Or possibly to_date('01 2004', 'IW IYYY'), depending on taste.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/