Timestamp problems...wrong weeks.

From: "Campano, Troy" <Troy(dot)Campano(at)LibertyMutual(dot)com>
To: "Pgsql-General" <pgsql-general(at)postgresql(dot)org>
Subject: Timestamp problems...wrong weeks.
Date: 2004-04-29 18:42:49
Message-ID: 82C3FD729CFA504CA44E7D32BDED411F099C8792@lm-exmsg-07.lm.lmig.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Dann Corbit 2004-04-29 18:43:12 Re: Arbitrary precision modulo operation
Previous Message Jon Pastore 2004-04-29 18:04:47 Re: postgresql idle