Thread: Timestamp problems...wrong weeks.

Timestamp problems...wrong weeks.

From
"Campano, Troy"
Date:

Hello,

Im 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

Re: Timestamp problems...wrong weeks.

From
Andrew Sullivan
Date:
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

Re: Timestamp problems...wrong weeks.

From
"Campano, Troy"
Date:
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)

Re: Timestamp problems...wrong weeks.

From
Jean-Luc Lachance
Date:
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
>


Re: Timestamp problems...wrong weeks.

From
DeJuan Jackson
Date:
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 May
>
>This 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;

Re: Timestamp problems...wrong weeks.

From
Tom Lane
Date:
"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

Re: Timestamp problems...wrong weeks.

From
Andrew Sullivan
Date:
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