Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] ISO week dates

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Brendan Jurd <direvus(at)gmail(dot)com>
Cc: Guillaume Lelarge <guillaume(at)lelarge(dot)info>, Peter Eisentraut <peter_e(at)gmx(dot)net>, pgsql-patches(at)postgresql(dot)org
Subject: Re: [GENERAL] ISO week dates
Date: 2006-11-18 02:26:21
Message-ID: 200611180226.kAI2QL318343@momjian.us (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-patches
This has been saved for the 8.3 release:

	http://momjian.postgresql.org/cgi-bin/pgpatches_hold

---------------------------------------------------------------------------

Brendan Jurd wrote:
> The attached patch implements my proposal to extend support for the
> ISO week date calendar.
> 
> I have added two new format fields for use with to_char, to_date and
> to_timestamp:
>     - ID for day-of-week
>     - IDDD for day-of-year
> 
> This makes it possible to convert ISO week dates to and from text
> fully represented in either week ('IYYY-IW-ID') or day-of-year
> ('IYYY-IDDD') format.
> 
> I have also added an 'isoyear' field for use with extract / date_part.
> 
> The patch includes documentation updates and some extra tests in the
> regression suite for the new fields.
> 
> I have tried to implement these features with as little disruption to
> the existing code as possible.  I built on the existing date2iso*
> functions in src/backend/utils/adt/timestamp.c, and added a few
> functions of my own, but I wonder if these functions would be more
> appropriately located in datetime.c, alongside date2j and j2date?
> 
> I'd also like to raise the topic of how conversion from text to ISO
> week dates should be handled, where the user has specified a bogus
> mixture of fields.  Existing code basically ignores these issues; for
> example, if a user were to call to_date('1998-01-01 2454050',
> 'YYYY-MM-DD J') the function returns 2006-01-01, a result of setting
> the year field from YYYY, then overwriting year, month and day with
> the values from the Julian date in J, then setting the month and day
> normally from MM and DD.
> 
> 2006-01-01 is not a valid representation of either of the values the
> user specified.  Now you might say "ask a silly question, get a silly
> answer"; the user shouldn't send nonsense arguments to to_date and
> expect a sensible result.  But perhaps the right way to respond to a
> broken timestamp definition is to throw an error, rather than behave
> as though everything has gone to plan, and return something which is
> not correct.
> 
> The same situation can arise if the user mixes ISO and Gregorian data;
> how should Postgres deal with something like to_date('2006-250',
> 'IYYY-DDD')?  The current behaviour in my patch is actually to assume
> that the user meant to say 'IYYY-IDDD', since "the 250th Gregorian day
> of the ISO year 2006" is total gibberish.  But perhaps it should be
> throwing an error message.
> 
> That's all for now, thanks for your time.
> BJ

[ Attachment, skipping... ]

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

-- 
  Bruce Momjian   bruce(at)momjian(dot)us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

In response to

pgsql-patches by date

Next:From: Bruce MomjianDate: 2006-11-18 02:26:36
Subject: Re: [HACKERS] Indicate disabled triggers in \d
Previous:From: Robert TreatDate: 2006-11-17 23:25:19
Subject: add external pl language section, clean up some links

pgsql-general by date

Next:From: Bruce MomjianDate: 2006-11-18 02:28:06
Subject: Re: select result / functions from another database
Previous:From: Stephen HarrisDate: 2006-11-18 02:22:05
Subject: Re: Shutting down a warm standby database in 8.2beta3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group