pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDATESTYLE and 0000-00-00
4+ messages / 4 participants
[nested] [flat]
* DATESTYLE and 0000-00-00
@ 2005-10-06 13:01 Joshua Kramer <josh@globalherald.net>
2005-10-06 18:15 ` Re: DATESTYLE and 0000-00-00 Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-06 18:16 ` Re: DATESTYLE and 0000-00-00 D'Arcy J.M. Cain <darcy@druid.net>
2005-10-06 18:37 ` Re: DATESTYLE and 0000-00-00 Michael Fuhr <mike@fuhr.org>
0 siblings, 3 replies; 4+ messages in thread
From: Joshua Kramer @ 2005-10-06 13:01 UTC (permalink / raw)
To: pgsql-sql
Greetings,
I have my DATESTYLE set to ISO MDY.
When I try to create a table with a default date of 0000-00-00, psql says
that this is an invalid date. Why, and can (or how can I) get it to
accept 0000-00-00 as a valid date?
Thanks,
-Josh
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DATESTYLE and 0000-00-00
2005-10-06 13:01 DATESTYLE and 0000-00-00 Joshua Kramer <josh@globalherald.net>
@ 2005-10-06 18:15 ` Andrew Sullivan <ajs@crankycanuck.ca>
2 siblings, 0 replies; 4+ messages in thread
From: Andrew Sullivan @ 2005-10-06 18:15 UTC (permalink / raw)
To: pgsql-sql
On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote:
> I have my DATESTYLE set to ISO MDY.
>
> When I try to create a table with a default date of 0000-00-00, psql says
> that this is an invalid date. Why, and can (or how can I) get it to
> accept 0000-00-00 as a valid date?
You can't. There's no year 0. As the docs say, if you don't like
that, please complain to the Vatican; we can't help you.
If you are trying to say, "Date unknown," you can use NULL (and maybe
add another field to indicate whether the NULL means "no date" or
"date unknown").
Note that your datestyle has nothing to do with this: Postgres won't
let you put an invalid date into a date field. This also has the
happy consequence that you can't accidentally create leap years where
there aren't any:
andrewtest=# SELECT '2005-02-29'::date;
ERROR: date/time field value out of range: "2005-02-29"
If you really think you have to have such dates, you are probably
mistaken. If you have to have them because some application you
can't control relies on them, you could store them in a text column,
and coerce them to dates when you select (but be prepared for the
errors you'll get). But my suggestion is that if you really think
you have to have such dates, you should redesign (which might mean
"replace") your application. Anything that uses dates with a year 0
is so fundamentally mistaken about how dates work that I wouldn't
trust it.
--
Andrew Sullivan | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
--Brad Holland
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DATESTYLE and 0000-00-00
2005-10-06 13:01 DATESTYLE and 0000-00-00 Joshua Kramer <josh@globalherald.net>
@ 2005-10-06 18:16 ` D'Arcy J.M. Cain <darcy@druid.net>
2 siblings, 0 replies; 4+ messages in thread
From: D'Arcy J.M. Cain @ 2005-10-06 18:16 UTC (permalink / raw)
To: Joshua Kramer <josh@globalherald.net>; +Cc: pgsql-sql
On Thu, 6 Oct 2005 09:01:22 -0400 (EDT)
Joshua Kramer <josh@globalherald.net> wrote:
> I have my DATESTYLE set to ISO MDY.
>
> When I try to create a table with a default date of 0000-00-00, psql says
> that this is an invalid date. Why, and can (or how can I) get it to
> accept 0000-00-00 as a valid date?
It is invalid. There was no year 0. Perhaps what you want is NULL or,
possibly, EPOCH if you are looking for a sentinel value.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DATESTYLE and 0000-00-00
2005-10-06 13:01 DATESTYLE and 0000-00-00 Joshua Kramer <josh@globalherald.net>
@ 2005-10-06 18:37 ` Michael Fuhr <mike@fuhr.org>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2005-10-06 18:37 UTC (permalink / raw)
To: Joshua Kramer <josh@globalherald.net>; +Cc: pgsql-sql
On Thu, Oct 06, 2005 at 09:01:22AM -0400, Joshua Kramer wrote:
> I have my DATESTYLE set to ISO MDY.
>
> When I try to create a table with a default date of 0000-00-00, psql says
> that this is an invalid date. Why, and can (or how can I) get it to
> accept 0000-00-00 as a valid date?
Why do you want to, considering that 0000-00-00 is *not* a valid
date? Why not use NULL to represent an unknown value?
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-10-06 18:37 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-10-06 13:01 DATESTYLE and 0000-00-00 Joshua Kramer <josh@globalherald.net>
2005-10-06 18:15 ` Andrew Sullivan <ajs@crankycanuck.ca>
2005-10-06 18:16 ` D'Arcy J.M. Cain <darcy@druid.net>
2005-10-06 18:37 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox