Обсуждение: 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@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/