Timestamp problems...wrong weeks.

[Campano, Troy]

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

[Andrew Sullivan]

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 | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Sullivan]

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 | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Jean-Luc Lachance]

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:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Jean-Luc Lachance]

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:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Campano, Troy" <Tr**********@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

"Campano, Troy" <Tr**********@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Andrew Sullivan]

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 | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Andrew Sullivan]

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 | aj*@crankycanuck.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)