Re: [GENERAL] converting a specified year and week into a date
-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 -MM-DD format. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF0sHFS9HxQb37XmcRAqClAJ4zkTJU7hT4vSbNM/8HyRqJwbSc1wCfeGJk Tqr6q1YDe+TajGEY50Bl26Y= =8i3I -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] converting a specified year and week into a date
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 -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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
-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 -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- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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 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-') || ' - ' || to_char(datum+(3+7*(kw-1)||'days')::interval,'dd-mm-'); 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
-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- ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] converting a specified year and week into a date
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] converting a specified year and week into a date
-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- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] converting a specified year and week into a date
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 ');
to_date
2004-01-01
(1 row)
select to_date('02 2004', 'WW ');
to_date
2004-01-08
(1 row)
--
Alban Hertroys
[EMAIL PROTECTED]
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 //
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] converting a specified year and week into a date
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 ');
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
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] converting a specified year and week into a date
Am Mittwoch, 14. Februar 2007 11:53 schrieb Alban Hertroys:
I think you're looking for this:
select to_date('01 2004', 'WW ');
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/
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
