Timestamp problems...wrong weeks.
Hello,
I'm having a problem with timestamps in postgresql.
I run the following query to pull dates in this format:
WEEK/MONTH/YEAR
However the data says that April 28th 2004 was in week 4 of April and
that April 29th 2004 was in week 5 of april.
This is incorrect.
This is causing my reports to print out incorrect data.
Do you know why this would happen? Am I doing something wrong?
--SQL CODE
SELECT
current_timestamp,
completion_date,
to_char(current_timestamp,'W/MM/YYYY'),
to_char(completion_date,'W/MM/YYYY')
FROM anna_onestop_database_t
WHERE to_char(current_timestamp,'MM/YYYY') =
to_char(completion_date,'MM/YYYY')
AND upper(solution_provider) = 'N0050961' AND status LIKE 'Closed -
Completed'
--RESULTSET
Timestamptz |completion_date |to_char
|to_char
2004-04-29 14:29:47.289369-04|2004-04-28 11:40:35|5/04/2004|4/04/2004
2004-04-29 14:29:47.289369-04|2004-04-29 13:26:34|5/04/2004|5/04/2004
thank you!
Troy Campano
On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
This is causing my reports to print out incorrect data.
Do you know why this would happen? Am I doing something wrong?
I think you may be misunderstanding what "W" means:
week of month (1-5) (The first week starts on the first day of the
month.)
In April 2004, 1 April is Thurs, so
1-7 -> W1
8-14 -> W2
15-21 -> W3
22-28 -> W4
29-30 -> W5 == W1 of May
This is also why 8 May is in week 2 of May, but 7 May is on week 1.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
Is there any way with SQL to get what I'm trying to get?
Where (in this month, April):
April 1 - 3 (Week 1)
April 4 - 10 (Week 2)
April 11 - 17 (Week 3)
April 18 - 24 (Week 4)
April 25 - 30 (Week 5)
Thank you!
~ Troy Campano ~
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Thursday, April 29, 2004 3:42 PM
To: Pgsql-General
Subject: Re: [GENERAL] Timestamp problems...wrong weeks.
On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
This is causing my reports to print out incorrect data.
Do you know why this would happen? Am I doing something wrong?
I think you may be misunderstanding what "W" means:
week of month (1-5) (The first week starts on the first day of the
month.)
In April 2004, 1 April is Thurs, so
1-7 -> W1
8-14 -> W2
15-21 -> W3
22-28 -> W4
29-30 -> W5 == W1 of May
This is also why 8 May is in week 2 of May, but 7 May is on week 1.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
Import Notes
Resolved by subject fallback
Personnaly, I think this use of W is useless -- anybody can dewide the
day of the month by 7. A more appropriate use of W might be for the
week number to start with the first week with ALL dates in the month.
An alternate interpretation might be to start with the first week that
has at least one day in the month. The next issue is do you start the
week on Sunday, Saturday or Monday...
JLL
Andrew Sullivan wrote:
Show quoted text
On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
This is causing my reports to print out incorrect data.
Do you know why this would happen? Am I doing something wrong?I think you may be misunderstanding what "W" means:
week of month (1-5) (The first week starts on the first day of the
month.)In April 2004, 1 April is Thurs, so
1-7 -> W1
8-14 -> W2
15-21 -> W3
22-28 -> W4
29-30 -> W5 == W1 of MayThis is also why 8 May is in week 2 of May, but 7 May is on week 1.
A
Campano, Troy wrote:
Is there any way with SQL to get what I'm trying to get?
Where (in this month, April):
April 1 - 3 (Week 1)
April 4 - 10 (Week 2)
April 11 - 17 (Week 3)
April 18 - 24 (Week 4)
April 25 - 30 (Week 5)Thank you!
~ Troy Campano ~
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andrew Sullivan
Sent: Thursday, April 29, 2004 3:42 PM
To: Pgsql-General
Subject: Re: [GENERAL] Timestamp problems...wrong weeks.On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
This is causing my reports to print out incorrect data.
Do you know why this would happen? Am I doing something wrong?I think you may be misunderstanding what "W" means:
week of month (1-5) (The first week starts on the first day of the
month.)In April 2004, 1 April is Thurs, so
1-7 -> W1
8-14 -> W2
15-21 -> W3
22-28 -> W4
29-30 -> W5 == W1 of MayThis is also why 8 May is in week 2 of May, but 7 May is on week 1.
A
take a look
select date_part('week', CURRENT_DATE) -
date_part('week', date_trunc('month', CURRENT_DATE)) + 1;
"Campano, Troy" <Troy.Campano@LibertyMutual.com> writes:
However the data says that April 28th 2004 was in week 4 of April and
that April 29th 2004 was in week 5 of april.
This is incorrect.
This *is* correct according to our published specification for the W
format code:
W week of month (1-5) (The first week starts on the first day of the month.)
Since you haven't defined what behavior you want, it's hard to suggest
alternatives, but have you looked at the WW and IW format codes?
regards, tom lane
On Thu, Apr 29, 2004 at 05:06:00PM -0400, Jean-Luc Lachance wrote:
Personnaly, I think this use of W is useless -- anybody can dewide the
day of the month by 7.
I didn't write the definition of the behaviour, I was just explaining
how it actually works.
A
--
Andrew Sullivan | ajs@crankycanuck.ca