converting a specified year and week into a date

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 :)

--
View this message in context: http://www.nabble.com/converting-a-specified-year-and-week-into-a-date-tf3223753.html#a8954235
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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(at)magproductions(dot)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/