Обсуждение: [GENERAL] Typecasting datetype as date. How do I cope with NULLs?

Dear All,

For those of you who don't want to wade through the details, here's the
question: "How do I get the date portion of a datetime field for ALL
ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
Typecasting a datetime NULL as date generates an error)"

details...

I have a table which stores, among other information, people's Date of
Birth as a datetime datatype called dob (I don't use the 'time' part but
the datatype is supported by many more functions).  Of course, I don't
always know the birthday of the person involved so the filed sometimes has
a NULL value.

patients=> \d patients

Table    = patients
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                | Length|
+----------------------------------+----------------------------------+-------+
| patient_id                       | char() not null                  |    16 |
| surname                          | text                             |   var |
| firstname                        | text                             |   var |
| othernames                       | text                             |   var |
| dob                              | datetime                         |     8 |
| sex                              | char()                           |     1 |
+----------------------------------+----------------------------------+-------+
Index:    patients_new_pkey
patients=>

I have a select on this table which typecasts the dob (Date of Birth) as
date i.e.:

patients=> SELECT surname,firstname,othernames,dob::date FROM patients;
ERROR:  Unable to convert null datetime to date
patients=>

I find myself having to do two selects to get all the people in the table.
i.e.:

patients=> SELECT surname,firstname,othernames,dob::date FROM patients
WHERE dob
 IS NOT NULL;
surname|firstname|othernames          |      date
-------+---------+--------------------+----------
Goose  |Mother   |Lay Golden Eggs     |11-01-1923
One    |Un       |Uno Ein             |11-11-1111
Light  |Dee      |Full                |22-01-1933
(3 rows)

patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL;
surname|firstname|othernames
-------+---------+----------
Rison  |Stuart   |
Rison  |This     |Pal
Rison  |Mark     |
(3 rows)

My question is, how do I get surname,firstname,othername and the date
portion of ALL people in table people regardless of whether the entry has
an actual dob or a NULL dob.

The best I have managed so far is:

patients=> SELECT surname,firstname,othernames,date_part('day',dob) as dd,
patients-> date_part('month',dob) as mm, date_part('year',dob) as yyyy
patients-> FROM patients;
surname|firstname|othernames          |dd|mm|yyyy
-------+---------+--------------------+--+--+----
Goose  |Mother   |Lay Golden Eggs     |11| 1|1923
One    |Un       |Uno Ein             |11|11|1111
Light  |Dee      |Full                |22| 1|1933
Rison  |Stuart   |                    |  |  |
Rison  |This     |Pal                 |  |  |
Rison  |Mark     |                    |  |  |
(6 rows)

patients=>

But I would like to have the date of birth as one field rather than 3.  I
have tried concatenating with || (no joy, date_part returns a float8 so
can't cat) and textcat (same problem).  Typecasting the return value of
date_part does not work (e.g.:
patients=> select date_part('day',dob)::text ||
date_part('month',dob)::text fro
m patients;
?column?
--------------------------------------------------------
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:01 2000 GMT
Sat 01 Jan 00:00:01 2000 GMTSat 01 Jan 00:00:04 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT
Sat 01 Jan 00:00:11 2000 GMTSat 01 Jan 00:00:11 2000 GMT

(6 rows)

patients=> select
textcat(date_part('day',dob)::text,date_part('month',dob)::text);
same output!

CAN IT BE DONE???

cheers,

Stuart.




+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

>>>>> "SR" == Stuart Rison <stuart@ludwig.ucl.ac.uk> writes:

 SR> Dear All,
 SR> For those of you who don't want to wade through the details, here's the
 SR> question: "How do I get the date portion of a datetime field for ALL
 SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
 SR> Typecasting a datetime NULL as date generates an error)"

. . .

 SR> I find myself having to do two selects to get all the people in the table.
 SR> i.e.:

 SR> patients=> SELECT surname,firstname,othernames,dob::date FROM patients
 SR> WHERE dob
 SR> IS NOT NULL;
 SR> surname|firstname|othernames          |      date
 SR> -------+---------+--------------------+----------
 SR> Goose  |Mother   |Lay Golden Eggs     |11-01-1923
 SR> One    |Un       |Uno Ein             |11-11-1111
 SR> Light  |Dee      |Full                |22-01-1933
 SR> (3 rows)

 SR> patients=> SELECT surname,firstname,othernames FROM patients WHERE dob IS NULL;
 SR> surname|firstname|othernames
 SR> -------+---------+----------
 SR> Rison  |Stuart   |
 SR> Rison  |This     |Pal
 SR> Rison  |Mark     |
 SR> (3 rows)

 SR> My question is, how do I get surname,firstname,othername and the date
 SR> portion of ALL people in table people regardless of whether the entry has
 SR> an actual dob or a NULL dob.

There are, on my mind, at least two answers. For experience I use
small table 'create table a( dt datetime, i int)'. Hera are data in
this table (one row has NULL as dt value):

tolik=> select * from a;
dt                          | i
----------------------------+--
Thu Nov 26 16:35:23 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
                            |10

First use 'union':
-----------------------------------------------
select dt::date, i from a where dt is not null
union
select NULL,     i from a where dt is null;
      date| i
----------+--
11-25-1998| 2
11-26-1998| 1
11-27-1998| 3
          |10
(4 rows)
-----------------------------------------------


Second, try use date_trunc('day', dt) instead date_part:
--------------------------------------------------------------
tolik=> select date_trunc('day', dt), i from a;
date_trunc                  | i
----------------------------+--
Thu Nov 26 00:00:00 1998 MSK| 1
Wed Nov 25 00:00:00 1998 MSK| 2
Fri Nov 27 00:00:00 1998 MSK| 3
                            |10
(4 rows)

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

Regards!

--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer

>There are, on my mind, at least two answers. For experience I use
>small table 'create table a( dt datetime, i int)'. Hera are data in
>this table (one row has NULL as dt value):
>
>tolik=> select * from a;
>dt                          | i
>----------------------------+--
>Thu Nov 26 16:35:23 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>
>First use 'union':
>-----------------------------------------------
>select dt::date, i from a where dt is not null
>union
>select NULL,     i from a where dt is null;
>      date| i
>----------+--
>11-25-1998| 2
>11-26-1998| 1
>11-27-1998| 3
>          |10
>(4 rows)
>-----------------------------------------------
>
>
>Second, try use date_trunc('day', dt) instead date_part:
>--------------------------------------------------------------
>tolik=> select date_trunc('day', dt), i from a;
>date_trunc                  | i
>----------------------------+--
>Thu Nov 26 00:00:00 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>(4 rows)
>
>--------------------------------------------------------------
>
I find the above interesting and I've tried something similar that won't
work.
I have two tables defined as follows

create table test1 (id1 int,link1 int);
create table test2 (id2 int, field2 varchar(5));

where link1 is a foreign key of test1 that should be linked to id2 of test2

Now when I execute the following query:

select id1,field2,link1 from test1,test2 where test1.link1=test2.id2
union
select id1,NULL,link1 from test1;

I always get the following error:

Each UNION query must have identical target types.

Why this error, and what does it mean?

TIA
Wim Ceulemans - wim.ceulemans@nice.be
Nice Software Solutions
Eglegemweg 3, 2811 Hombeek - Belgium
Tel +32(0)15 41 29 53 - Fax +32(0)15 41 29 54

>>>>> "WC" == Wim Ceulemans <wim.ceulemans@nice.be> writes:


 WC> I find the above interesting and I've tried something similar that won't
 WC> work.
 WC> I have two tables defined as follows

 WC> create table test1 (id1 int,link1 int);
 WC> create table test2 (id2 int, field2 varchar(5));

 WC> where link1 is a foreign key of test1 that should be linked to id2 of test2

 WC> Now when I execute the following query:

 WC> select id1,field2,link1 from test1,test2 where test1.link1=test2.id2
 WC> union
 WC> select id1,NULL,link1 from test1;

 WC> I always get the following error:

 WC> Each UNION query must have identical target types.

 WC> Why this error, and what does it mean?

Pehaps this is a bug fixed in 6.4 version? I saw this message when
before upgrating to 6.4. You can modify query so:

select id1,field2,link1 from test1,test2 where test1.link1=test2.id2
 union
select id1, -1, link1 from test1;

where -1 is value non present in field2 domain.

--
Anatoly K. Lasareff              Email:       tolik@icomm.ru
Senior programmer

> SR> Dear All,
> SR> For those of you who don't want to wade through the details, here's the
> SR> question: "How do I get the date portion of a datetime field for ALL
> SR> ENTRIES in a table regardless of whether the entry is NULL or not? (N.B.
> SR> Typecasting a datetime NULL as date generates an error)"
>

<snip>

>
>There are, on my mind, at least two answers. For experience I use
>small table 'create table a( dt datetime, i int)'. Hera are data in
>this table (one row has NULL as dt value):
>
>tolik=> select * from a;
>dt                          | i
>----------------------------+--
>Thu Nov 26 16:35:23 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>
>First use 'union':
>-----------------------------------------------
>select dt::date, i from a where dt is not null
>union
>select NULL,     i from a where dt is null;
>      date| i
>----------+--
>11-25-1998| 2
>11-26-1998| 1
>11-27-1998| 3
>          |10
>(4 rows)

I had not thought of using a UNION, thanks for that (well that's not
strictly true, I was 'procedurally' implemeting it rather then using SQL!!)
so this is a definite possibility.
I still feel that this equates to two SQL queries since the backend will
have to process each individually and then UNION them (is this right?).  My
suggestion of:

patients=> SELECT surname,firstname,othernames,date_part('day',dob) as dd,
patients-> date_part('month',dob) as mm, date_part('year',dob) as yyyy
patients-> FROM patients;
surname|firstname|othernames          |dd|mm|yyyy
-------+---------+--------------------+--+--+----
Goose  |Mother   |Lay Golden Eggs     |11| 1|1923
One    |Un       |Uno Ein             |11|11|1111
Light  |Dee      |Full                |22| 1|1933
Rison  |Stuart   |                    |  |  |
Rison  |This     |Pal                 |  |  |
Rison  |Mark     |                    |  |  |
(6 rows)

means only one query need to be executed by the backend and it can cope
with NULL, but it 'generates' three date fields and I would like only one.

>
>Second, try use date_trunc('day', dt) instead date_part:
>--------------------------------------------------------------
>tolik=> select date_trunc('day', dt), i from a;
>date_trunc                  | i
>----------------------------+--
>Thu Nov 26 00:00:00 1998 MSK| 1
>Wed Nov 25 00:00:00 1998 MSK| 2
>Fri Nov 27 00:00:00 1998 MSK| 3
>                            |10
>(4 rows)
>
>--------------------------------------------------------------


Again, this solves one part of my problem (i.e. the query functions even if
I datetime is NULL) but what I'm looking for is something that ONLY shows
the date portion of a datetime (e.g. Thu Nov 26 16:35:23 1998 MSK becomes
26-11-1998) so the date_trunc masking is not quite what I'm looking for.

Would it be possible to do a select datetime and then output only the
dateday, datemonth and dateyear using an RE??

All the same, thanks for your suggestion Anatoly, UNION's the winner so far ;)

regards,

S.



+-------------------------+--------------------------------------+
| Stuart Rison            | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street               |
| Tel. (0171) 878 4041    | London, W1P 8BT, UNITED KINGDOM.     |
| Fax. (0171) 878 4040    | stuart@ludwig.ucl.ac.uk              |
+-------------------------+--------------------------------------+

At 13:12 +0200 on 27/11/98, Stuart Rison wrote:


>
> Again, this solves one part of my problem (i.e. the query functions even if
> I datetime is NULL) but what I'm looking for is something that ONLY shows
> the date portion of a datetime (e.g. Thu Nov 26 16:35:23 1998 MSK becomes
> 26-11-1998) so the date_trunc masking is not quite what I'm looking for.
>
> Would it be possible to do a select datetime and then output only the
> dateday, datemonth and dateyear using an RE??
>
> All the same, thanks for your suggestion Anatoly, UNION's the winner so
>far ;)

Well, first I'd like to say that not converting null datetimes is
definitely a bug and should be placed in the ToDo list for the next version.

That said, I tried several conversions, using the following table:

testing=> select * from test1;
f1          |dt
------------+----------------------------
This year   |Fri Jan 02 00:00:00 1998 IST
Next year   |Mon Mar 15 00:00:00 1999 IST
Nothing     |
Last year   |Mon May 12 00:00:00 1997 IDT
More nothing|
(5 rows)

The one that finally worked is the following:

testing=> select f1, date( abstime( dt ) ) from test1;
f1          |      date
------------+----------
This year   |01-02-1998
Next year   |03-15-1999
Nothing     |
Last year   |05-12-1997
More nothing|
(5 rows)

That is, convert to abstime, and then to date...

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma