Обсуждение: 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!
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)
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 >
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;
"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