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_timesta mp,
completion_date ,
to_char(current _timestamp,'W/MM/YYYY'),
to_char(complet ion_date,'W/MM/YYYY')
FROM anna_onestop_da tabase_t
WHERE to_char(current _timestamp,'MM/YYYY') =
to_char(complet ion_date,'MM/YYYY')
AND upper(solution_ provider) = 'N0050961' AND status LIKE 'Closed -
Completed'

--RESULTSET
Timestamptz |completion_dat e |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]

Re: Timestamp problems...wron g weeks.

On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:[color=blue]
>
> This is causing my reports to print out incorrect data.
> Do you know why this would happen? Am I doing something wrong?[/color]

I think you may be misunderstandin g 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@crankycanuc k.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Andrew Sullivan]

Re: Timestamp problems...wron g weeks.

On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:[color=blue]
>
> This is causing my reports to print out incorrect data.
> Do you know why this would happen? Am I doing something wrong?[/color]

I think you may be misunderstandin g 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@crankycanuc k.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Jean-Luc Lachance]

Re: Timestamp problems...wron g weeks.

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:
[color=blue]
> On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
>[color=green]
>>This is causing my reports to print out incorrect data.
>>Do you know why this would happen? Am I doing something wrong?[/color]
>
>
> I think you may be misunderstandin g 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
>[/color]


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

[Jean-Luc Lachance]

Re: Timestamp problems...wron g weeks.

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:
[color=blue]
> On Thu, Apr 29, 2004 at 02:42:49PM -0400, Campano, Troy wrote:
>[color=green]
>>This is causing my reports to print out incorrect data.
>>Do you know why this would happen? Am I doing something wrong?[/color]
>
>
> I think you may be misunderstandin g 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
>[/color]


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

[Tom Lane]

Re: Timestamp problems...wron g weeks.

"Campano, Troy" <Troy.Campano@L ibertyMutual.co m> writes:[color=blue]
> 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.[/color]

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]

Re: Timestamp problems...wron g weeks.

"Campano, Troy" <Troy.Campano@L ibertyMutual.co m> writes:[color=blue]
> 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.[/color]

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]

Re: Timestamp problems...wron g weeks.

On Thu, Apr 29, 2004 at 05:06:00PM -0400, Jean-Luc Lachance wrote:[color=blue]
> Personnaly, I think this use of W is useless -- anybody can dewide the
> day of the month by 7.[/color]

I didn't write the definition of the behaviour, I was just explaining
how it actually works.

A

--
Andrew Sullivan | ajs@crankycanuc k.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)

[Andrew Sullivan]

Re: Timestamp problems...wron g weeks.

On Thu, Apr 29, 2004 at 05:06:00PM -0400, Jean-Luc Lachance wrote:[color=blue]
> Personnaly, I think this use of W is useless -- anybody can dewide the
> day of the month by 7.[/color]

I didn't write the definition of the behaviour, I was just explaining
how it actually works.

A

--
Andrew Sullivan | ajs@crankycanuc k.ca

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to majordomo@postg resql.org)