Postgres Pro
Downloads
Home   >   mailing lists

Thread: Validity check in to_date?

Validity check in to_date?

From
"Alexander M. Pravking"
Date:
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,
compiledby GCC 2.95.4
 


-- 
Fduch M. Pravking

Re: Validity check in to_date?

From
Christoph Haller
Date:
> 
> 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

Re: Validity check in to_date?

From
Karel Zak
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/

Re: Validity check in to_date?

From
"Alexander M. Pravking"
Date:
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

Re: Validity check in to_date?

From
"Alexander M. Pravking"
Date:
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

Re: Validity check in to_date?

From
"Iain"
Date:
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

Re: Validity check in to_date?

From
"Alexander M. Pravking"
Date:
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

Re: Validity check in to_date?

From
"Alexander M. Pravking"
Date:
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

Re: Validity check in to_date?

From
Stephan Szabo
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).

Re: Validity check in to_date?

From
"Iain"
Date:
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

Re: Validity check in to_date?

From
CoL
Date:
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