pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedValidity check in to_date?
11+ messages / 6 participants
[nested] [flat]
* Validity check in to_date?
@ 2003-11-27 14:11 Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 2 replies; 11+ messages in thread
From: Alexander M. Pravking @ 2003-11-27 14:11 UTC (permalink / raw)
To: pgsql-sql
I just discovered that to_date() function does not check if supplied
date is correct, giving surprising (at least for me) results:
fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
to_date
------------
2003-12-01
or even
fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
to_date
------------
2007-01-03
to_timestamp() seems to work the same way. It's probably useful sometimes,
but not in my case... Is it how it supposed to work?
If so, how can I do such a validity check?
If not, has something changed in 7.4?
In any case, I have to find a workaround now and will appreciate any help.
fduch=# SELECT version();
version
---------------------------------------------------------------------
PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 09:57 Karel Zak <zakkr@zf.jcu.cz>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 2 replies; 11+ messages in thread
From: Karel Zak @ 2003-12-02 09:57 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: Alexander M. Pravking <fduch@antar.bryansk.ru>; pgsql-sql
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> >
> > I just discovered that to_date() function does not check if supplied
> > date is correct, giving surprising (at least for me) results:
> >
> > fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> > to_date
> > ------------
> > 2003-12-01
> >
> > or even
> >
> > fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> > to_date
> > ------------
> > 2007-01-03
> >
> > to_timestamp() seems to work the same way. It's probably useful sometimes,
> > but not in my case... Is it how it supposed to work?
> > If so, how can I do such a validity check?
> > If not, has something changed in 7.4?
No change in 7.4. Maybe in 7.5 or in some 7.4.x.
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
> I don't know of any postgres function doing the check you're looking for.
> But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
The others PostgreSQL stuff which full parse (means check ranges)
date/time is less optimistic with this:
# select '31.11.2003'::date;
ERROR: date/time field value out of range: "31.11.2003"
Karel
--
Karel Zak <zakkr@zf.jcu.cz>
http://home.zf.jcu.cz/~zakkr/
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:17 CoL <col@mportal.hu>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
1 sibling, 0 replies; 11+ messages in thread
From: CoL @ 2003-12-02 10:17 UTC (permalink / raw)
To: pgsql-sql
hi,
SELECT isfinite(timestamp '123.45.2003'); if this is true, the date is
ok, if error, than not :)
C.
Alexander M. Pravking wrote:
> I just discovered that to_date() function does not check if supplied
> date is correct, giving surprising (at least for me) results:
>
> fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> to_date
> ------------
> 2003-12-01
>
> or even
>
> fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> to_date
> ------------
> 2007-01-03
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:27 Christoph Haller <ch@rodos.fzk.de>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
1 sibling, 2 replies; 11+ messages in thread
From: Christoph Haller @ 2003-12-02 10:27 UTC (permalink / raw)
To: fduch@antar.bryansk.ru; +Cc: pgsql-sql
>
> I just discovered that to_date() function does not check if supplied
> date is correct, giving surprising (at least for me) results:
>
> fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> to_date
> ------------
> 2003-12-01
>
> or even
>
> fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> to_date
> ------------
> 2007-01-03
>
> to_timestamp() seems to work the same way. It's probably useful sometimes,
> but not in my case... Is it how it supposed to work?
> If so, how can I do such a validity check?
> If not, has something changed in 7.4?
>
> In any case, I have to find a workaround now and will appreciate any help.
>
>
> fduch=# SELECT version();
> version
> ---------------------------------------------------------------------
> PostgreSQL 7.3.4 on i386-portbld-freebsd4.8, compiled by GCC 2.95.4
>
>
> --
> Fduch M. Pravking
>
As far as I know these results are correct in terms of the underlying
C-library function mktime(). This function is intended to be used when
adding/subtracting intervals from a given timestamp.
I don't know of any postgres function doing the check you're looking for.
But I can't believe this is the first time this topic is brought up.
You may search the archives on "date plausibility" are related terms.
HTH
Regards, Christoph
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:30 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Christoph Haller <ch@rodos.fzk.de>
1 sibling, 0 replies; 11+ messages in thread
From: Alexander M. Pravking @ 2003-12-02 10:30 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> As far as I know these results are correct in terms of the underlying
> C-library function mktime(). This function is intended to be used when
> adding/subtracting intervals from a given timestamp.
Which one? mktime() or to_date()? I'm not sure it's handy to use
to_date() for any calculations, so I'm surprised why doesn't it work
just as date_in() do.
> I don't know of any postgres function doing the check you're looking for.
Yes, the only thing I could think now is to do something like
s/([0-9]+)\.([0-9]+)\.([0-9]+)/\3-\2-\1/ and then pass it to
CAST(... AS date) using ISO DateStyle.
(I could simply use German DateStyle in case of DD.MM.YYYY, but I deal
with several date formats, e.g. DD/MM/YYYY.)
> But I can't believe this is the first time this topic is brought up.
> You may search the archives on "date plausibility" are related terms.
I'm sure too, but it's really hard to find a good keyword sequence when
searching such sort of things :(
Anyway, thank you for attention.
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:36 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Karel Zak <zakkr@zf.jcu.cz>
1 sibling, 0 replies; 11+ messages in thread
From: Alexander M. Pravking @ 2003-12-02 10:36 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Tue, Dec 02, 2003 at 10:57:14AM +0100, Karel Zak wrote:
> > > If not, has something changed in 7.4?
>
> No change in 7.4. Maybe in 7.5 or in some 7.4.x.
Well, let's see.
> The others PostgreSQL stuff which full parse (means check ranges)
> date/time is less optimistic with this:
>
> # select '31.11.2003'::date;
> ERROR: date/time field value out of range: "31.11.2003"
Exactly! But date_in formats are too limited and "floaty", especially
in 7.3 or less.
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:44 Iain <iain@mst.co.jp>
parent: Karel Zak <zakkr@zf.jcu.cz>
1 sibling, 2 replies; 11+ messages in thread
From: Iain @ 2003-12-02 10:44 UTC (permalink / raw)
To: Karel Zak <zakkr@zf.jcu.cz>; Christoph Haller <ch@rodos.fzk.de>; +Cc: Alexander M. Pravking <fduch@antar.bryansk.ru>; pgsql-sql
T've been following this thread with interest because I have a related
problem. Basically we are storing dates in CHAR fields with al the
associated problems. I'd like to do it, but changing everything to date
fields isn't practical for now, so as a stopgap solution, I want to provide
some validation at the database level.
I tried:
create domain ymdtest2 as char(10) constraint valid_date check
(VALUE::DATE);
But it gives this error:
ERROR: cannot cast type character to date
I also tried:
create domain test char(10) check (CAST(VALUE AS DATE));
and it gives the same error. I don't need to actually modify VALUE, but I'd
like to have it so that any attempt to insert an invalid date will cause the
transaction to fail. This is OK:
SELECT CAST('2003-3-31' AS DATE);
So it is possible to cast type character to date (as we all know) so it
seems that the problem only applies to checks.
Now, I am wondering if there is a clever work around to this? It escapes me
for now anyway.
Regards
Iain
----- Original Message -----
From: "Karel Zak" <zakkr@zf.jcu.cz>
To: "Christoph Haller" <ch@rodos.fzk.de>
Cc: "Alexander M. Pravking" <fduch@antar.bryansk.ru>;
<pgsql-sql@postgresql.org>
Sent: Tuesday, December 02, 2003 6:57 PM
Subject: Re: [SQL] Validity check in to_date?
> On Tue, Dec 02, 2003 at 10:27:04AM +0100, Christoph Haller wrote:
> > >
> > > I just discovered that to_date() function does not check if supplied
> > > date is correct, giving surprising (at least for me) results:
> > >
> > > fduch=# SELECT to_date('31.11.2003', 'DD.MM.YYYY');
> > > to_date
> > > ------------
> > > 2003-12-01
> > >
> > > or even
> > >
> > > fduch=# SELECT to_date('123.45.2003', 'DD.MM.YYYY');
> > > to_date
> > > ------------
> > > 2007-01-03
> > >
> > > to_timestamp() seems to work the same way. It's probably useful
sometimes,
> > > but not in my case... Is it how it supposed to work?
> > > If so, how can I do such a validity check?
> > > If not, has something changed in 7.4?
>
> No change in 7.4. Maybe in 7.5 or in some 7.4.x.
>
> > As far as I know these results are correct in terms of the underlying
> > C-library function mktime(). This function is intended to be used when
> > adding/subtracting intervals from a given timestamp.
> > I don't know of any postgres function doing the check you're looking
for.
> > But I can't believe this is the first time this topic is brought up.
> > You may search the archives on "date plausibility" are related terms.
>
> The others PostgreSQL stuff which full parse (means check ranges)
> date/time is less optimistic with this:
>
> # select '31.11.2003'::date;
> ERROR: date/time field value out of range: "31.11.2003"
>
> Karel
>
> --
> Karel Zak <zakkr@zf.jcu.cz>
> http://home.zf.jcu.cz/~zakkr/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:55 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Iain <iain@mst.co.jp>
1 sibling, 1 reply; 11+ messages in thread
From: Alexander M. Pravking @ 2003-12-02 10:55 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Karel Zak <zakkr@zf.jcu.cz>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Tue, Dec 02, 2003 at 07:44:54PM +0900, Iain wrote:
> T've been following this thread with interest because I have a related
> problem. Basically we are storing dates in CHAR fields with al the
> associated problems. I'd like to do it, but changing everything to date
> fields isn't practical for now, so as a stopgap solution, I want to provide
> some validation at the database level.
>
> I tried:
>
> create domain ymdtest2 as char(10) constraint valid_date check
> (VALUE::DATE);
There's no conversion function from char(n) to date, but there's one
from text to date. Try using check (VALUE::text::date).
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 10:58 Alexander M. Pravking <fduch@antar.bryansk.ru>
parent: Alexander M. Pravking <fduch@antar.bryansk.ru>
0 siblings, 0 replies; 11+ messages in thread
From: Alexander M. Pravking @ 2003-12-02 10:58 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Karel Zak <zakkr@zf.jcu.cz>; Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
On Tue, Dec 02, 2003 at 01:55:06PM +0300, Alexander M. Pravking wrote:
> Try using check (VALUE::text::date).
Assuming check expects boolean result, it's (VALUE::text::date IS NOT NULL)
--
Fduch M. Pravking
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-02 16:15 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Iain <iain@mst.co.jp>
1 sibling, 1 reply; 11+ messages in thread
From: Stephan Szabo @ 2003-12-02 16:15 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Karel Zak <zakkr@zf.jcu.cz>; Christoph Haller <ch@rodos.fzk.de>; Alexander M. Pravking <fduch@antar.bryansk.ru>; pgsql-sql
On Tue, 2 Dec 2003, Iain wrote:
> T've been following this thread with interest because I have a related
> problem. Basically we are storing dates in CHAR fields with al the
> associated problems. I'd like to do it, but changing everything to date
> fields isn't practical for now, so as a stopgap solution, I want to provide
> some validation at the database level.
>
> I tried:
>
> create domain ymdtest2 as char(10) constraint valid_date check
> (VALUE::DATE);
>
> But it gives this error:
>
> ERROR: cannot cast type character to date
>
> I also tried:
>
> create domain test char(10) check (CAST(VALUE AS DATE));
I'd try CAST(CAST(VALUE AS TEXT) AS DATE)
There's a text->date conversion, but not one from character(n).
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Validity check in to_date?
@ 2003-12-03 02:18 Iain <iain@mst.co.jp>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 11+ messages in thread
From: Iain @ 2003-12-03 02:18 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; Alexander M. Pravking <fduch@antar.bryansk.ru>; +Cc: pgsql-sql
Sweeet!
This is what I ended up with:
create domain testdate char(10) check (VALUE::text::date = VALUE);
(it wasn't possible to insert a NULL date with (VALUE::text::date IS NOT
NULL); )
I'm pretty happy with this as I didn't even have to use
to_char(VALUE::text::date , 'YYYY-MM-DD') for the comparison, since the
date format I am using matches the "datestyle" setting.
It seems to accept any valid date, as well as NULL, while rejecting inputs
such as:
insert into test1 values ('2002-03-32');
insert into test1 values ('200-03-22');
insert into test1 values ('2002- 3-22');
insert into test1 values ('2002-03-2');
insert into test1 values ('2002-03- 2');
insert into test1 values ('2002-3-2');
insert into test1 values ('2002-14-02');
insert into test1 values ('');
insert into test1 values ('2002/03/22');
insert into test1 values ('2002/03/32');
insert into test1 values ('200/03/22');
insert into test1 values ('2002/ 3/22');
insert into test1 values ('2002/03/2');
insert into test1 values ('2002/03/ 2');
insert into test1 values ('2002/3/2');
insert into test1 values ('2002/14/02');
It's no silk purse, but it's short and sweet and I'm satisfied.
Thanks guys.
Rregards
Iain
----- Original Message -----
From: "Stephan Szabo" <sszabo@megazone.bigpanda.com>
To: "Iain" <iain@mst.co.jp>
Cc: "Karel Zak" <zakkr@zf.jcu.cz>; "Christoph Haller" <ch@rodos.fzk.de>;
"Alexander M. Pravking" <fduch@antar.bryansk.ru>; <pgsql-sql@postgresql.org>
Sent: Wednesday, December 03, 2003 1:15 AM
Subject: Re: [SQL] Validity check in to_date?
> On Tue, 2 Dec 2003, Iain wrote:
>
> > T've been following this thread with interest because I have a related
> > problem. Basically we are storing dates in CHAR fields with al the
> > associated problems. I'd like to do it, but changing everything to date
> > fields isn't practical for now, so as a stopgap solution, I want to
provide
> > some validation at the database level.
> >
> > I tried:
> >
> > create domain ymdtest2 as char(10) constraint valid_date check
> > (VALUE::DATE);
> >
> > But it gives this error:
> >
> > ERROR: cannot cast type character to date
> >
> > I also tried:
> >
> > create domain test char(10) check (CAST(VALUE AS DATE));
>
> I'd try CAST(CAST(VALUE AS TEXT) AS DATE)
>
> There's a text->date conversion, but not one from character(n).
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2003-12-03 02:18 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-27 14:11 Validity check in to_date? Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-12-02 10:17 ` CoL <col@mportal.hu>
2003-12-02 10:27 ` Christoph Haller <ch@rodos.fzk.de>
2003-12-02 09:57 ` Karel Zak <zakkr@zf.jcu.cz>
2003-12-02 10:36 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-12-02 10:44 ` Iain <iain@mst.co.jp>
2003-12-02 10:55 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-12-02 10:58 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
2003-12-02 16:15 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-12-03 02:18 ` Iain <iain@mst.co.jp>
2003-12-02 10:30 ` Alexander M. Pravking <fduch@antar.bryansk.ru>
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